Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
paweln
Contributor III
Contributor III

FirstValue() and LastValue(), what about 2nd, 3rd values?

Dear All,

I use FirstValue() and LastValue() script functions. How can I get second or third value?

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

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.

commQV54.PNG

View solution in original post

7 Replies
Vegar
MVP
MVP

Your should be able to fetch your second, third or fourth value by FirstSortedValue() somewhat using load order as your ranking criteria. 

aveeeeeee7en
Specialist III
Specialist III

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

 

paweln
Contributor III
Contributor III
Author

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.

paweln_0-1592686418943.png

 

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

Saravanan_Desingh

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
];
Saravanan_Desingh

Output.

commQV53.PNG

paweln
Contributor III
Contributor III
Author

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?

Saravanan_Desingh

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.

commQV54.PNG