Number automatically showing in millions in the output data of select tool

[nicholaskoh003]

Putting it out here, I have two input source and the number values were set as int64 and integer values for both datasets may contain integers up to 10 digits. Having said that, one of the output in the select tool displayed the number subsequently in millions automatically while the other one did not. 

I was unable to locate anything which I can toggle to prevent the select tool in displaying the data in millions. Hopefully I might chance upon some advise regarding this here, thanks!

[Luke_C]

Hi @nicholaskoh003 

Can you share some sample data or screenshots of the issue? Would be helpful to troubleshoot.

[gabrielvilella]

Alteryx will display thousand separator when the field is set to a numeric type and you click on the 000 icon on the top right corner of the results area. You probably had one field that had numbers in it, but wasn't set to a numeric type.

[Qiu]

@gabrielvilella 
Nice one.
Learnd a new trick! 😁

[nicholaskoh003]

@Luke_C you may use the key value "413270" in Account No field as an example. It was shown to display the values in field Month 1, Month 2 etc. in millions (input vs output).  

[nicholaskoh003]

@gabrielvilella  Thanks for sharing, unfortunately I think the thousand separator is not relevant to the scenario that I am facing.

[gabrielvilella]

You have extra characters such as parenthesis and thousands separator that needs to be removed before doing the conversion of the field to Int64. Since you have this happening on multiple fields, I recommend using the multi field formula. You can even change the field type in it as I did here.

[nicholaskoh003]

@gabrielvilella Ahh, understand. I tried to convert those with parenthesis to negative integer on your example with the following expressions but it seemed to run with error. Will you be able to shed some light on this?

ToNumber(ReplaceChar([_CurrentField_],',',''))
IF Contains ([_CurrentField_],'()')
THEN ToNumber(ReplaceChar([_CurrentField_],'()','')) * -1
ELSE [_CurrentField_]

[gabrielvilella · Accepted solution]

Try this one:

IF Contains ([_CurrentField_],'(')
THEN ToNumber(ReplaceChar([_CurrentField_],'(),','')) * -1
ELSE ToNumber(ReplaceChar([_CurrentField_],',',''))
ENDIF

[nicholaskoh003]

@gabrielvilella Thank you! this works!