Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

formatting numbers

Abhii2658
8 - Asteroid

Hello ! 

 

I have incorrect formats in the below table that i want to correct. The fields in red are incorrect and want them changed as shown in the table 

I want the output for the first 3 rows changed as shown in desired output. 

 

Field OutputDesired Output 
21.840.0002221,840,000
1.077.719.25511,077,719,255
1.012.065.60011,012,065,600
287.245287 
123456789123,456,789 
1234512,345 
170027800170,027,800 
123.456123 
7 REPLIES 7
binay2448
11 - Bolide

Find my solution...

Abhii2658
8 - Asteroid

Thanks @binay2448 

But 287.245 should be 287 and not 287,245. 

Abhii2658
8 - Asteroid

Similarly, 123.456 should be 123

binay2448
11 - Bolide

How should we can differentiate decimal before last 3 digit numbers in these cases

shreyanshrathod
11 - Bolide

@Abhii2658 , How are you determining that 287.xxx should be 287 and 1.234.xxx.xxx should be 1,234,xxx,xxx?

The way I see it then, you have to apply a conditional formula (IF statement) and replace "." with ",".

 

Regards,

Shreyansh

Elias_Nordlinder
11 - Bolide

Hello @Abhii2658 ,

 

Here is one way I did that could solve your problem as you state it at the moment.

But as other says, it depends on how you decide to differentiate between 287.245 and the other numbers.


(I took it as 287.245 in the beginning means 287,245, which is rounded to 287,
while 12,345 means 12 346, which can be written as 12.346.
So all numbers that have specifically "One Dot" is actually in the hundreds and have decimals written out, while all the other numbers does not.)

 

Elias_Nordlinder_0-1629645594098.png

'

 

Elias_Nordlinder_0-1629645316288.png

 

 

 

1. Use RecordID to be able to join back and keep the original order in the end of the workflow.

 

2. Count number of Dots in the number

 

3. Split on the Length of the Number,

-> Take the upper Route if more than 2 dots or 0 dots

-> Take the lower Route if 1 dot

 

4. Upper Route is similar as the previous solution by @binay2448 

 

5. Lower Route changes to Int32 (Thereby automatically removes decimal places).

 

6. Union the two routes together on position.

 

7. Join back on the original data on RecordID to keep the position of each row.

 

//Let me know if this solves your problem 🙂

 

Regards
Elias

 

 

 

Abhii2658
8 - Asteroid

Thanks a lot everyone, i will check the logic one more time, looks like i am missing something. 

Labels