Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need to create a filter on a Qlik Sense dashboard to return the latest result from a table. Let's say I have the follwoing table:
Name | Subject | Score | Date |
---|---|---|---|
John | Mathematics | 2 | 1.1.2017 |
Marc | Mathematics | 5 | 1.1.2017 |
John | Mathematics | 3 | 15.2.2017 |
Peter | Mathematics | 1 | 15.2.2017 |
John | Mathematics | 4 | 7.3.2017 |
In this example, John is displayed 3 times for the same subject. How can I make a filter to display only his latest results? i.e. the one on 7.3.2017?
The actual table has hundreds of records, so ideally the filter will display only the latest results for the users having multiple records with similar names and subjects.
May be like this??
You can create a flag in the script
Table:
LOAD Name,
Subject,
Score,
Date
FROM ....;
Left Join (Table)
LOAD Name,
Subject,
Max(Date) as Date,
1 as MaxDateFlag
Resident Table
Group By Name, Subject;
Now to view only the MaxDate information, you can either make a selection in MaxDateFlag or you can use it in your set analysis
{<MaxDateFlag = {1}>}
UPDATE: Modified based on Kushal's response
just modification to Sunny's script
Left Join (Table)
LOAD Name,
Subject,
Max(Date) as Date,
1 as MaxDateFlag
Resident Table
Group by Name,Subject;
Thanks for pointing that out
What if you have a scenarios where you need to pick the max date based on your current selection for each person (not overall)?