Get the last value for a specific field

[#1]

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!

[rwunderlich · Accepted solution]

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

=FirstSortedValue(OBS, -PERIOD)

-Rob

[JonnyPoole]

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.

[#4]

Thanks! It worked by using:

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

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

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

[rafael_qg]

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