Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I use FirstValue() and LastValue() script functions. How can I get second or third value?
Use Rank parameter to get the 2nd, 3rd, or more..
FirstSortedValue(Value, RowID, 2)
Use Count(ID) to check if it has only one Value. Based on that you can take decision on how do you want to show.
Your should be able to fetch your second, third or fourth value by FirstSortedValue() somewhat using load order as your ranking criteria.
Hi Paweln,
You can use RowNo() or RecNo() for getting 2nd & 3rd value. It will generate a Record or Row Number. Using that field you can write IF condition to get the values.
Hope that helps.
Regards,
Av7eN
For better explenation:
ID numbers have usually one value but sometimes it can be 2 values (see the picture). In such case FirtsValue() and LastValue() works perfect but sometimes one ID can have even 5 different values. there are thousands of IDs.
FirstSortedValue doesn't work because there's nothing to use as a second argument.
Finally I'd like to achieve ID in first column (no duplicates) and columns value1, value2, value3, value4, value5
Are you looking something like this?
tab1:
LOAD RowNo() As RowID,* INLINE [
ID, Value
z_67, 1282F
z_68, 1938E
z_69, 1938E
z_70, 1282F
z_70, 1940E
z_71, 1282F
z_71, 1940E
z_72, 1940E
z_79, 1282F
z_79, 1938E
z_80, 1282F
];
Output.
Thank you but it doesn't work.
Using this I can get First or second value but what if there is only one value for ID number? I still get two (the same) values.
And how to get 3rd, 4th, 5th vale using this?
Use Rank parameter to get the 2nd, 3rd, or more..
FirstSortedValue(Value, RowID, 2)
Use Count(ID) to check if it has only one Value. Based on that you can take decision on how do you want to show.