Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
New-Qlik
Creator III
Creator III

Date based on max row id

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

1 Solution

Accepted Solutions
anushree1
Specialist II
Specialist II

Please try the below expression :

=only({<Row_id={$(=max(Row_id))}>}Date)

View solution in original post

13 Replies
JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
New-Qlik
Creator III
Creator III
Author

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

JordyWegman
Partner - Master
Partner - Master

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

Work smarter, not harder
New-Qlik
Creator III
Creator III
Author

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
Partner - Master
Partner - Master

Hi, 

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

Jordy

Climber

Work smarter, not harder
anushree1
Specialist II
Specialist II

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

New-Qlik
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder