Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Announcements
Qlik's 15-week Applied Data Analytics using Qlik Sense course. Classes start Jan. 17, 2022. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

latest status based on a date

Hi,

I am new to Qlikview, wonder if someone can help me with expressions in Qlikview-

See below an applicant history where row number represents the sequence of activities happened to that application. I need to identify the latest status (SA_TO_STATUS) on a date. For e.g. the status of this application  on 17/12/2012 is PGIOFFER which is the latest status to that date. Status on 31/12/2012 is PGIACCO, on 14/12/2012 is PGIOFFER. In SQL I would do

select  sa_to_status where sa_to_created_date=(select max(sa_to_created_date) where sa_to_created_date<=input_date AND sa_to_status IS NOT NULL).

Can we do this in qlikview?

Thanks a lot!

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Try to include a set analysis, maybe like this:

firstsortedvalue({< Field = {'*'}>} Field, -Date)

- Marcus

View solution in original post

4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Have a look on the function firtssortedvalue().

- Marcus

Not applicable
Author

Great! Thanks Marcus. This works if no null values in STATUS field. I am not sure how I can ignore those null records. For 20/12/2012, I would like to ignore any null entries and show PGIOFFER

marcus_sommer
MVP & Luminary
MVP & Luminary

Try to include a set analysis, maybe like this:

firstsortedvalue({< Field = {'*'}>} Field, -Date)

- Marcus

View solution in original post

Not applicable
Author

Wow!  That works!!! Excellent!

Thank you very much!

Community Browser