04-12-2022 11:05 AM
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
Solved! Go to Solution.
04-12-2022 11:21 AM
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)
04-12-2022 11:21 AM
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)
04-12-2022 11:29 AM
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
04-12-2022 11:43 AM
It worked! I tried testing with parseInt($ID) and I got the desired results, thanks again @bojanvelevski 🙂 :innocent:
04-12-2022 12:10 PM
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.