Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tincholiver
Creator III
Creator III

Max value

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!

3 Solutions

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder

View solution in original post

nilaksh92
Partner - Contributor III
Partner - Contributor III

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

View solution in original post

tincholiver
Creator III
Creator III
Author

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!

View solution in original post

4 Replies
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
nilaksh92
Partner - Contributor III
Partner - Contributor III

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

sergio0592
Specialist III
Specialist III

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');
tincholiver
Creator III
Creator III
Author

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!