Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to findout latest records with their status

Dear Experties,

How to find out latest record with latest status.please see the below example table :

    

IDStartDateDaysStatusReason
A100101/01/2017 10:08:1110closed
A100101/01/2017 11:08:1115Pendinglatest record
A100131/01/2017 10:08:1130closed
A100201/02/2017 12:10:1312Pending
A100221/02/2017 12:10:1315closedLatest Record

i would like to expecting like below

   

IDStartDateDaysStatusReason
A100101/01/2017 11:08:1115Pending
A100221/02/2017 12:10:1315-

It would be helpfull if anyone can provide  the logic.


Regards,

Sunny

1 Solution

Accepted Solutions
swuehl
MVP
MVP

ID Maxstring(StartDate) FirstSortedValue(Days, -StartDate) FirstSortedValue(StatusReason, -StartDate)
A100131.01.2017 10:08:1130closed
A100221.02.2017 12:10:1315closed

View solution in original post

10 Replies
swuehl
MVP
MVP

For A1001, shouldn't the record with 31/12/2017 be picked?

In general, you can create a table chart with dimension ID and use FirstSortedValue in your expression:

=FirstSortedValue( StatusReason, -StartDate)

This requires that StartDate shows a numeric representation, i.e. the timestamp should be correctly interpreted as such.

Set the TimestampFormat to your format to support a correct interpretation in your script.

Not applicable
Author

Hi Stefan ,

Thank you for the reply,it was typo .it is  31/01/2017  10:08:11.

Not applicable
Author

Hi,

I  need to know all the column information same like below not only for ID and status reason

IDStartDateDaysStatusReason
A100101/01/2017 11:08:1115Pending
A100221/02/2017 12:10:1315-
swuehl
MVP
MVP

You can find the StartDate with

=MaxString(StartDate)

in a chart with dimension ID, and respective Days and StatusReason can be retrieved using FirstSortedValue() as indicated above.

Not applicable
Author

Hi ,

I am not able to get exactly output result .the below results i am getting .

sample.PNG

swuehl
MVP
MVP

Seems like you are using additional dimensions, Days and StatusReason. Only use the dimension you want your results to group by, ID as I understood.

vkish16161
Creator III
Creator III

Stefan,

If this has to be done at the script level, could we not use ApplyMap (This has limitation for 2 fields)

But is it possible?

Thanks!

swuehl
MVP
MVP

The straight forward analogon to the suggested chart expressions would be a GROUP BY clause with the same functions:

LOAD

     ID,

     Maxstring(StartDate) as StartDate,

     FirstSortedValue(StatusReason, -StartDate) as StatusReason

FROM ...

GROUP BY ID;

Sunni, if your data shows more than one record with the same max StartDate, the FirstSortedValue() either needs the DISTINCT qualifier or you need to add a small value to the sort weight for tie breaking.

Not applicable
Author

Hi Stefan,

I am still facing issue with days and status Reason,it would be helpful if you can  provide a solution  with attached sample data.