Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Get the last value for a specific field

Hello,

I have the following table:

IDPERIODOBS
005420130131 - Desc 31
005420130205YR - Desc 05YR
005420130309YR - Desc 09YR
005420140409YR - Desc 09YR
005420140409YR - Desc 09YR
005420140509YR - Desc 09YR
005420140609NR - Desc 09NR
005420140720NR - Desc 20NR

Where I need to build a table to show if the OBS = 31 / 09YR / 09NR are available for that account or not.

For example, if there is an OBS = 31, I should show a 1, if not a 0.

I thought of creating a straight table and add dimension ID and 3 expressions (one for every OBS in columns):

=SUBSTRINGCOUNT(CONCAT(DISTINCT OBS,'|'),'31')

=SUBSTRINGCOUNT(CONCAT(DISTINCT OBS,'|'),'09YR')

=SUBSTRINGCOUNT(CONCAT(DISTINCT OBS,'|'),'09NR')

It works fine, but now I was told to find for OBS = 09YR / 09NR the last one depending on the PERIOD (YYYY/MM).

In this example, I have:

IDPERIODOBS
005420140509YR - Desc 09YR
005420140609NR - Desc 09YR

So I should get for this ID that I have 09NR (represented with 1) and no 09YR (represented with 0):

IDOBS 31OBS 09YROBS 09NR
0054101

Is there any way to add something more to the expressions I'm using in my straight table to get the last OBS like I mentioned?

Thank you!

1 Solution

Accepted Solutions
rwunderlich

To get the last  OBS field by PERIOD value, use the FirstSortedValue() function.

=FirstSortedValue(OBS, -PERIOD)

-Rob

View solution in original post

4 Replies
rwunderlich

To get the last  OBS field by PERIOD value, use the FirstSortedValue() function.

=FirstSortedValue(OBS, -PERIOD)

-Rob

JonnyPoole
Employee
Employee

You can use FirstSortedValue() to find the last value.  The second argument would be    -Period so that it inverts the rows by period and takes the first (actually most recent) period.  The first arguement is the expression you are looking to retrieve.

Not applicable
Author

Thanks! It worked by using:

=SUBSTRINGCOUNT(FirstSortedValue(OBS, -PERIOD),'31')

=SUBSTRINGCOUNT(FirstSortedValue(OBS, -PERIOD),'09YR')

=SUBSTRINGCOUNT(FirstSortedValue(OBS, -PERIOD),'09NR')

rafael_qg
Partner - Contributor III
Partner - Contributor III

Hi, 

I have the same question and your solution works fine but the performance with a big table is very bad.

There is another solution for this case?

Perhaps, order the table on script and another function with a better performance?

Thanks a lot