topic Re: Get the last value for a specific field in QlikView

Get the last value for a specific field

Fri, 14 Nov 2014 17:46:48 GMT

Hello,

I have the following table:

ID	PERIOD	OBS
0054	201301	31 - Desc 31
0054	201302	05YR - Desc 05YR
0054	201303	09YR - Desc 09YR
0054	201404	09YR - Desc 09YR
0054	201404	09YR - Desc 09YR
0054	201405	09YR - Desc 09YR
0054	201406	09NR - Desc 09NR
0054	201407	20NR - 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:

ID	PERIOD	OBS
0054	201405	09YR - Desc 09YR
0054	201406	09NR - Desc 09YR

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

ID	OBS 31	OBS 09YR	OBS 09NR
0054	1	0	1

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!

Re: Get the last value for a specific field

rwunderlich — Fri, 14 Nov 2014 18:24:03 GMT

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

=FirstSortedValue(OBS, -PERIOD)

-Rob

Re: Get the last value for a specific field

JonnyPoole — Fri, 14 Nov 2014 18:27:15 GMT

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.

Re: Get the last value for a specific field

Fri, 14 Nov 2014 19:18:18 GMT

Thanks! It worked by using:

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

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

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

Re: Get the last value for a specific field

rafael_qg — Mon, 11 Nov 2019 08:45:47 GMT

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