Qlik Community

Qlik Sense App Development

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

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

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.

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

Re: Date based on max row id

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

Highlighted
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