Convert real column to string formatted column (with dollar sign and thousands separator)

[Kem Azlan]

Hi, I just want to check it out whether is there a way to convert some column (in type real) into string with format where (can add $ and thousands separator) using Spotfire expression? I try to avoid using TERR or Python script to convert them since it may impact the engine in server when loading the dashboard.

[Kirsten Smith (she/her)]

Does the column have to be a String?  If no, you can click on Data/Column Properties, highlight this column, then click on the Formatting tab.   Select 'Currency' and check the "Use thousands separator" and Spotfire will re-format it as you have described above.

If it needs to be a string, then you can use the Data/Transform data option with "Calculate and replace column" and "Change data type" transformations.

[Kem Azlan]

Hi Kirsten, yes, the column should be in String since I try to append the table later after I manage to convert as above. 

I tried on Data/Transform data option, but it not worked as expected.  

Edited May 20 by Kem Azlan

[Kem Azlan]

Hi @Kirsten Smith (she/her). I might figure out on the formula to convert the column. Here is:

If(Len(String([REVENUE]))<16,Concatenate('$',If(Len(Split(String([REVENUE]),'.',1))<=3,Split(String([REVENUE]),'.',1),If(Len(Split(String([REVENUE]),'.',1))<=6,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 3),',',right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=9,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 6),',',Mid(Split(String([REVENUE]),'.',1),3,3),',',right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=12,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 9),',',Mid(Split(String([REVENUE]),'.',1),3,3),',',Mid(Split(String([REVENUE]),'.',1),6,3),',',right(Split(String([REVENUE]),'.',1),3)),' ')))),'.',Split(String([REVENUE]),'.',2)),'')

Noted that the formula works for only positive value and up to billions. 

[Kem Azlan]

Ignore formula above. Here is the correct formula: 

If(Len(String([REVENUE]))<16,Concatenate('$',If(Len(Split(String([REVENUE]),'.',1))<=3,Split(String([REVENUE]),'.',1),If(Len(Split(String([REVENUE]),'.',1))<=6,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 3),',',Right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=9,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 6),',',Mid(Split(String([REVENUE]),'.',1),2,3),',',Right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=12,Concatenate(left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 9),',',Mid(Split(String([REVENUE]),'.',1),2,3),',',Mid(Split(String([REVENUE]),'.',1),5,3),',',Right(Split(String([REVENUE]),'.',1),3)),' ')))),'.',Split(String([REVENUE]),'.',2)),'')

[Kem Azlan]

Ignore formula above. Here is the correct formula: 

If(Len(String([REVENUE]))<16,Concatenate('$',If(Len(Split(String([REVENUE]),'.',1))<=3,Split(String([REVENUE]),'.',1),If(Len(Split(String([REVENUE]),'.',1))<=6,Concatenate(Left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 3),',',Right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=9,Concatenate(Left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 6),',',Mid(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 5,3),',',Right(Split(String([REVENUE]),'.',1),3)),If(Len(Split(String([REVENUE]),'.',1))<=12,Concatenate(Left(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 9),',',Mid(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 8,3),',',Mid(Split(String([REVENUE]),'.',1),Len(Split(String([REVENUE]),'.',1)) - 5,3),',',Right(Split(String([REVENUE]),'.',1),3)),' ')))),'.',Split(String([REVENUE]),'.',2)),'')