Sort snap's functionality

[darshthakkar]

Results of a sort snap (ascending) is a little bit hard to digest. I used a file reader which was reading a single column A (i.e. IDs) and I decided to sort it. The results I get are surprising.

Input raw data:
1217
151983
1543
370904
402
402783
901602
373
373916
374
120651
1216
1000022
1000027
394533
906
909
1000019
918
1205

Output after sort:
1000019
1000022
1000027
1205
120651
1216
1217
151983
1543
370904
373
373916
374
394533
402
402783
901602
906
909
918

Result expected:
373
374
402
906
909
918
1205
1216
1217
1543
120651
151983
370904
373916
394533
402783
901602
1000019
1000022
1000027

Can anyone explain what’s the rationale behind the results I got using the sort snap? It’s confusing for me to be honest.
Thanking you in advance for your time, help and response.

Regards,
Darsh

[bojanvelevski · Accepted solution]

Hey @darshthakkar,

This happens because the field you’re sorting is string, so it is sorted alphabetically.

As I can see, all of these values support parsing so try and parse them before sorting.

Ex. : parseInt($ID)

[darshthakkar]

Thanks a TON @bojanvelevski, does using an “Excel Parser” after file reader help? Do we need to explicitly ingest a mapper and then put parseInt($ID) under expression?

Just for your reference, my pipeline looks like File Reader → Excel Parser → Mapper (as I’m renaming the single column, could have renamed it in excel itself before reading it but went this route) → Sort

[darshthakkar]

It worked! I tried testing with parseInt($ID) and I got the desired results, thanks again @bojanvelevski 🙂 :innocent:

[bojanvelevski]

You can use the same maper where you are renaming a column, and yes, implicitly parse the column you are sorting on.

Check the "Cell formatting" option, if "Formatted" is selected than I suppose the sorting field is coming as a string from the document. If you’re able to edit the file than you can try changing the whole column formatting to number.