I have to pick date where row_id is maximum .
Date Row_id Message
1/11/2019 12:00:00 1 Data Error
1/11/2019 12:10:00 2 Data Refreshed
I want to display second row . I tried max as script function but didn't work
Solved! Go to Solution.
Table: Load Max(Row_id) as RowFilter, ; Load * Inline [ Date, Row_id, Message 1/11/2019 12:00:00, 1 , Data Error 1/11/2019 12:10:00 , 2 , Data Refreshed ] ; Table: Load Date, Row_id, Message Where Row_id = RowFilter From [YourSource](qvd);
The Inline was just an example, you have to get your own database import for this.
You can also work with a variable:
Table: Load Date, Row_id, Message From [YourSource](qvd); tmpTable; Load Max(Row_id) as MaxRowId Resident Table; let vMaxRowId = peek('MaxRowId',0,'tmpTable') TableWithMaxRowId: Load Date, Row_id, Message Where Row_id = $(vMaxRowId) Resident Table;
Thanks Anushree. It workds wonders in table but when I am trying to create two KPT objects
1. Date =if(Row_id=max(all Row_id),Date)
2. Message =if(Row_id=max(all Row_id),message)
Its not working
Thanks Jordy. IT works for displaying date in KPI
but when I try to display message with Max_row_id = $(vMax_row_id) its give me '-'.
I am not sure where I am wrong
Check you variable overview (left corner) and see if the variable $(vMax_row_id) is returning a value here. If there is no value in here, then you haven't defined your variable well.