Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist III
Specialist III

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
Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
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

 

Highlighted
Contributor III
Contributor III

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

Highlighted
Specialist III
Specialist III

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
];
Highlighted
Specialist III
Specialist III

Output.

commQV53.PNG

Highlighted
Contributor III
Contributor III

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?

Highlighted
Specialist III
Specialist III

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