Date based on max row id

[New-Qlik]

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

[JordyWegman]

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

[New-Qlik]

Thanks Jordy for replying , but solution didn't work and also mine is a database table not inline.

[JordyWegman]

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

[New-Qlik]

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.

[JordyWegman]

Hi, 

Then you can just use the variable $(vMaxRowId) in the KPI object.

Jordy

Climber

[anushree1]

Please check the attached, is this what you are looking for?

[New-Qlik]

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

[New-Qlik]

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

[JordyWegman]

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