Max value

[tincholiver]

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!

[JordyWegman · Accepted solution]

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

[nilaksh92 · Accepted solution]

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]

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 · Accepted solution]

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!