topic Re: Get the Last Value in a Straight Table in QlikView

Get the Last Value in a Straight Table

julruiz123 — Fri, 27 Mar 2015 16:21:17 GMT

Hi!!

I have the next situation.. In a straight table with the Dimension of ID, i need add a expression that get the last value of the field Result like the next example.

ID Month Result

1 Jan -

1 Feb 5

1 Mar 2

1 Apr -

1 May 4

1 Jun -

The result tha i expect is:

ID Result

1 4

Thanks in advance.

Re: Get the Last Value in a Straight Table

buzzy996 — Fri, 27 Mar 2015 17:59:02 GMT

try,firstsortedvalue() with -1,pls look at reference manual for syntax.

Re: Get the Last Value in a Straight Table

Sat, 28 Mar 2015 13:01:46 GMT

Try this expression

Sum({<Month={'may'}>}Result)

and see the attached file

Re: Get the Last Value in a Straight Table

Alejandro_Hernández — Sat, 28 Mar 2015 22:24:01 GMT

Hola Julián,

intenta con

=only( {<Month={$(=max({<Result={">0"}>} Month))}>}Result)

Saludos

Re: Get the Last Value in a Straight Table

Anonymous — Sun, 29 Mar 2015 00:06:59 GMT

Hi Julian,

You could try to add a flag when loading the data. For instance if you called this flag "_MaxResult" the set expression that you need would be very simple:

sum({<_MaxResult = {1}>} Result)

And you will get the output that you need. This will also work if you need to visualize the last results for multiple IDs.

Review the attached filed for more details about the scripting used to generate the flag.

Best Regards

Re: Get the Last Value in a Straight Table

julruiz123 — Mon, 30 Mar 2015 16:42:51 GMT

Hola Alejandro,

De este modo me funciona sólo si la tabla tiene un ID, pero para varios ID's no funciona.

ID Month Result

1 Jan -

1 Feb 5

1 Mar 2

1 Apr -

1 May 4

1 Jun -

2 Jan -

2 Feb 3

2 Mar -

2 Apr -

En este caso obtiene a Mayo como el mes máximo con algún valor, entonces el resultado de la tabla solo va a traer el valor para el ID = 1.

Lo que espero es una tabla con el último valor para cada ID.

1 May 4

2 Feb 3