Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Please try the below expression :

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

View solution in original post

13 Replies
Highlighted
Partner
Partner

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
Highlighted
Creator II
Creator II

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

Highlighted
Partner
Partner

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
Highlighted
Creator II
Creator II

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.

Highlighted
Partner
Partner

Hi, 

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

Jordy

Climber

Work smarter, not harder
Highlighted
Specialist II
Specialist II

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

Highlighted
Creator II
Creator II

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

Highlighted
Creator II
Creator II

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

Highlighted
Partner
Partner

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