topic Re: Max value in QlikView

Max value

tincholiver — Wed, 25 Nov 2020 16:16:04 GMT

Hi everybody!

I'd like to show in a text object the last value recorded on a table.

Data is:

FECHA Valores
01/11/2018 -2,31%
01/10/2018 0,58%
01/09/2018 -2,22%
01/08/2018 0,85%
01/07/2018 1,08%
01/06/2018 -1,51%
01/05/2018 -1,62%
01/04/2018 -2,66%
01/03/2018 0,17%
01/02/2018 -0,18%
01/01/2018 0,65%

How can i do to show it? Result is -2,31 %

Thanks and regards!

Re: Max value

JordyWegman — Tue, 29 Jan 2019 15:13:00 GMT

Hi,

This is possible. Load all your data, order it descending based on the date and get your max value with a peek function. I did the resident load because I had to transform the date.

Table:
Load * Inline [
FECHA, Valores
01/11/2018,-2.31
01/10/2018,0.58
01/09/2018,-2.22
01/08/2018,0.85
01/07/2018,1.08
01/06/2018,-1.51
01/05/2018,-1.62
01/04/2018,-2.66
01/03/2018,0.17
01/02/2018,-0.18
01/01/2018,0.65
]
;

Table2:
NoConcatenate
Load
	Date(Date#(FECHA,'DD/MM/YYYY'),'DD-MM-YYYY') as FECHA,
    Valores
Resident Table
Order by FECHA desc
;

drop Table Table;

let vMaxValue = peek('Valores',0,'Table2');

Jordy

Climber

Re: Max value

nilaksh92 — Tue, 29 Jan 2019 15:15:26 GMT

Hi,

Create a variable vMaxDate=MaxString(FETCHA)

then in text box use set analysis like

=Only({<FETCHA={"$(=vMaxdate)"}>}Valores)

Hope this can solve your query.

Thanks

Nilaksh Mahajan

Re: Max value

sergio0592 — Tue, 29 Jan 2019 15:22:07 GMT

Or maybe

Table2:
NoConcatenate
Load
	max(Date(Date#(FECHA,'DD/MM/YYYY'),'DD-MM-YYYY')) as FECHA,
    Valores
Resident Table
group by Valores
;

drop Table Table;

let vMaxValue = peek('Valores',0,'Table2');

Re: Max value

tincholiver — Tue, 29 Jan 2019 16:18:27 GMT

Thanks buddies!

I made some changes using their tips and works very good:

Final result is something like this:

Table:
LOAD
FECHA,
'Group By' as Group_By, // make first a accumulator dimenssion
Valores
FROM Table.qvd(QVD);

NoConcatenate
Table2:
LOAD
Max(FECHA) as FECHA,
Group_By
Resident Table group by Group_By; // get the max FECHA in a new dimenssion

let vMaxValue = peek('FECHA',0,'Table2');// take the value in a variable

NoConcatenate
Final:
LOAD
FECHA,
$(vMaxValue) as Max_Date, // I add the value of the variable in the final table
Group_By,
Valores
Resident Table;
DROP Table Table2; // I discard the tables that I do not need
DROP Table Table; //I discard the tables that I do not need

// in expression: =Only({<FECHA={'$(=vMaxValue)'}>}Valores)

it's work!

Thanks a lot!