Originally posted by: NICK_R

Hi Team,

I am stuck with a decimal conversion while I was doing some maps as part of the migration project.

We have a different scenario that needs to be achieved through the wtx mapping .

Scenario 1 :need to convert the highlighted number to decimal format on the Right side. Convert to 2 decimal point, right justified & zero filled if no data available. It has a Leading +

Originally posted by: PaulBrett

Here's a couple of ways to do it in the attached example.

One uses the Typetree definitions, the second assumes all fields are text, and uses rules to achieve the same result.

Thank you.

Paul

Follow me on Twitter

Attachment(s)

NumConv.zip   3 KB 1 version

Originally posted by: NICK_R

 PaulBrett @ PaulBrett

Paul, I was actually looking for the Test2 solution from the provided example. I was able to change the logic slightly according to my needs. How ever the Logic in the example is not punching the decimal as per my requirement. 

Changed the Logic to :  = IF (TONUMBER(In1,"{####'.'2##2}")<=0,"+","+")+FILLLEFT(TEXT(TONUMBER(In1,"{####'.'2##2}")),"0",15)

Input value :  12345,04     or 00000,00

Required Output :    +000000012345.04      or    +000000000000.00

-- >  With in my code base I am able to get output as +000000000000000

Originally posted by: NICK_R

PaulBrett @ PaulBrett ,

Can you please help with the TEST 2 solution since that was not complete. I tried to change logic to convert this to the 2 decimal point , but did not had much luck.

Changed the Logic to :  = IF (TONUMBER(In1,"{####'.'2##2}")<=0,"+","+")+FILLLEFT(TEXT(TONUMBER(In1,"{####'.'2##2}")),"0",15)    

Input value :  12345,04     or 00000,00

Required Output :    +000000012345.04      or    +000000000000.00

-- >  With in my code base I am able to get output as +000000000000000

Originally posted by: PaulBrett

I didn't see that the input had a comma delimiter and your output required a decimal:

=IF(TONUMBER(In1,"{####'.'2##2}")<0,"-","+")+FILLLEFT(TEXT(TONUMBER(SUBSTITUTE(In1,",","."),"{####'.'2##2}")),"0",19)

Thank you.

Paul

Follow me on Twitter

Originally posted by: NICK_R

Tried this logic PaulBrett50DK7Q2WB6, but I am getting error : Argument #1 for function SUBSTITUTE is invalid.

=IF(TONUMBER(In1,"{####'.'2##2}")<0,"-","+")+FILLLEFT(TEXT(TONUMBER(SUBSTITUTE(In1,",","."),"{####'.'2##2}")),"0",19)

Input value :  12345,04     or 00000,00

Required Output :    +000000012345.04      or    +000000000000.00

Originally posted by: PaulBrett

Works for me.  This map assumes your In1 object is TEXT.

Thank you.

Paul

Follow me on Twitter

Attachment(s)

NumConvComma.zip   3 KB 1 version