Functions to "clean negative numbers with - sign at the end"

#1

Hello community,

I have a column that comes from CSV with the negative values like this 1234,56-

I only want to delete the last - and put in the start of the value. How you would do this?

I’ve tried to do something similar, but I think I can not put 2 funcions inside an IF. For example if ends with - "replace by “” and multiply by -1 or some similar approach.

if(endswith([Column 19],“-”),
char(code(“*”))&[Column 19],
[Column 19])

Thanks in advance!

#2

if(endswith([column 19], ‘-’), asnumber(replace((removeend([column 19],1)) , ‘,’ , ‘.’)) * -1,[column 19])

1 Like
#3

Wow, thanks is working. Only one question to understand, "Why you replace , for . "?

Thanks!

#4

Hi,
I think I’ve made it a little too quick and dirty :
if(endswith([column 19], ‘-’), asnumber(replace((removeend([column 19],1)) , ‘,’ , ‘.’)) * -1,
asnumber(replace([column 19] , ‘,’ , ‘.’)))
may works better
One thing to understand with EM : the data type is not define by the column (field) like in a database, each cell got his own type like in a spreadsheet. So you have to convert the string to num type. By default the , is a thousand separator while the . is the decimal separator. This is why I replaced , by .
Regards

1 Like
#5

Great and thanks the explanation.
inside a “true” or “false” condition in an IF can I execute 2 actions like when you are developing java or similar

action1;
action2;

Thanks!!

#6

Hi,
Think spreadsheet or multidim OLAP (TM1, essbase…) : one calculation, one result targetting one cell
hard to put 2 results in the same cell.
so you’ll have to make it at the workflow level : on true condition => go one way and execute several transformations, on false condition => go another way…
To sum up, think dataflow first : what data/results do I need for the next processing step ? then insert the calculation you need.
EM obliges to decompose each step but this also provides you a clear view of the impact of each step. You don’t walk blindly.
And put annotation everywhere because you can make very complex things without realizing it, till you have to explain them to an auditor. With annotations ypu just print the HTML project doc and voilà.
Hope it will help and welcome on board.

Regards

#7

thanks “super so much”!!