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

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!

Workflow

Datasets

Common Use Cases

Accepted answers

gabrielvilella

Try this one:

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

All comments

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

input select tool.JPG

output select tool.JPG

@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.

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

Try this one:

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703