Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to load a unique id and string into a table. The catch is there are multiple strings per unique id and I only want the longest string returned.
Data looks like this:
ID | String |
---|---|
1 | abc |
1 | abcdef |
1 | abcdefghi |
2 | xyz |
2 | zxypdq |
3 | lmn |
3 | lmnopq |
What i'd like to end up with is a table containing a list of distinct ID's and the longest string for that particular ID.
i.e.
ID | String |
---|---|
1 | abcdefghi |
2 | xyzpdq |
3 | lmnopq |
I've tried maxstring/minstring but i'm not able to get this to sort on string length or those may work.
I started looking at for loop options but that is much to slow for the dataset i'm working with.
Any ideas?
Hi,
Use this:
DATA:
LOAD * INLINE
[ ID, String
1, "1234, 5678 |"
3, "6789, 1234 |2345, 3456 |"
1, "1234, 8976 |1234, 4567 |"
2, "3456, 6789 |9012, 3456 |"
1, "1234, 5678 |8901, 2345 |3456, 4567 |"
2, "2345, 1234 |"
3, "6789, 1234 |2345, 3456 |2346, 9876 |"
3, "6789, 1234 |"
];
NoConcatenate
DATA2:
LOAD ID,
FirstSortedValue(Distinct String,-len(String)) as test
Resident DATA
Group By ID ;
Thanks
AJ
Hi,
Try this script:
DATA:
LOAD * INLINE [
ID, String
1, abc
1, abcdef
1, abcdefghi
2, xyz
2, zxypdq
3, lmn
3, lmnopq
];
NoConcatenate
DATA2:
LOAD
ID,
MaxString(String) as test
Resident DATA
Group By ID
;
drop table DATA;
Best,
Aurélien
Thanks for this. The example works well. My actual data wont always be sorted and looks more like this:
DATA:
LOAD * INLINE
[ ID, String
1, "1234, 5678 |"
3, "6789, 1234 |2345, 3456 |"
1, "1234, 8976 |1234, 4567 |"
2, "3456, 6789 |9012, 3456 |"
1, "1234, 5678 |8901, 2345 |3456, 4567 |"
2, "2345, 1234 |"
3, "6789, 1234 |2345, 3456 |2346, 9876 |"
3, "6789, 1234 |"
];
NoConcatenate
DATA2:
LOAD ID,
MaxString(String) as test
Resident DATA
Group By ID ;
Using the above code it seems to incorrectly choose the 2nd longest string for ID 1:
Hi,
Use this:
DATA:
LOAD * INLINE
[ ID, String
1, "1234, 5678 |"
3, "6789, 1234 |2345, 3456 |"
1, "1234, 8976 |1234, 4567 |"
2, "3456, 6789 |9012, 3456 |"
1, "1234, 5678 |8901, 2345 |3456, 4567 |"
2, "2345, 1234 |"
3, "6789, 1234 |2345, 3456 |2346, 9876 |"
3, "6789, 1234 |"
];
NoConcatenate
DATA2:
LOAD ID,
FirstSortedValue(Distinct String,-len(String)) as test
Resident DATA
Group By ID ;
Thanks
AJ
Thank you very much Ajay,
FirstSortedValue worked perfect in your example.