Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi
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
Thanks
Avneet
Please try the below expression :
=only({<Row_id={$(=max(Row_id))}>}Date)
Hi Avneet,
Try this:
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);
Jordy
Climber
Thanks Jordy for replying , but solution didn't work and also mine is a database table not inline.
Hi Avneet,
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;
Jordy
Climber
Thanks . I am trying to show message for max of ROW_ID in an KPI object. Set analysis is not working as message is a text.
Hi,
Then you can just use the variable $(vMaxRowId) in the KPI object.
Jordy
Climber
Please check the attached, is this what you are looking for?
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
Hi Avneet,
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.
Jordy
Climber