Qlik Community

Qlik Sense App Development

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

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
avneet85
Contributor

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
anushree1
Valued Contributor II

Re: Date based on max row id

Please try the below expression :

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

13 Replies
Partner
Partner

Re: Date based on max row id

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
avneet85
Contributor

Re: Date based on max row id

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

Partner
Partner

Re: Date based on max row id

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
avneet85
Contributor

Re: Date based on max row id

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

Re: Date based on max row id

Hi, 

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

Jordy

Climber

Work smarter, not harder
anushree1
Valued Contributor II

Re: Date based on max row id

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

avneet85
Contributor

Re: Date based on max row id

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

avneet85
Contributor

Re: Date based on max row id

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

Partner
Partner

Re: Date based on max row id

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