Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Need to find max date from Shipping Date field for respective ID's <='12/01/2019'
ID Shipping Date
1 4/3/2018 3:19:00 AM
1 10/22/2018 12:46:00 AM
1 10/22/2018 12:46:00 AM
1 2/11/2019 12:37:00 AM
1 2/11/2019 12:37:00 AM
1 7/23/2019 12:41:00 AM
1 7/23/2019 12:42:00 AM
1 1/7/2020 1:21:00 AM
1 1/7/2020 1:21:00 AM
1 2/11/2020 2:58:00 AM
1 2/11/2020 2:59:00 AM
2 10/20/2017 5:54:00 AM
2 1/17/2018 4:12:00 AM
2 12/7/2019 11:37:00 PM
3 10/22/2018 12:45:00 AM
3 10/22/2018 12:46:00 AM
3 2/11/2019 12:36:00 AM
3 2/11/2019 12:37:00 AM
3 6/7/2019 7:37:00 AM
3 1/7/2020 1:21:00 AM
3 1/28/2020 4:26:00 AM
Maybe something like this? It creates a key of ID|Shipping Date and then finds the max when grouped by ID and joins it to the initial load.
data:
NoConcatenate
Load *,
ID & '|'& [Shipping Date] as Max_Ship_Date_Key;
load * inline [
ID, Shipping Date
1, 4/3/2018 3:19:00 AM
1, 10/22/2018 12:46:00 AM
1, 10/22/2018 12:46:00 AM
1, 2/11/2019 12:37:00 AM
1, 2/11/2019 12:37:00 AM
1, 7/23/2019 12:41:00 AM
1, 7/23/2019 12:42:00 AM
1, 1/7/2020 1:21:00 AM
1, 1/7/2020 1:21:00 AM
1, 2/11/2020 2:58:00 AM
1, 2/11/2020 2:59:00 AM
2, 10/20/2017 5:54:00 AM
2, 1/17/2018 4:12:00 AM
2, 12/7/2019 11:37:00 PM
3, 10/22/2018 12:45:00 AM
3, 10/22/2018 12:46:00 AM
3, 2/11/2019 12:36:00 AM
3, 2/11/2019 12:37:00 AM
3, 6/7/2019 7:37:00 AM
3, 1/7/2020 1:21:00 AM
3, 1/28/2020 4:26:00 AM
];
data2:
Left Join(data)
load
ID & '|'& FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_Date_Key,
FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_date
Resident data
Where [Shipping Date] <= '12/1/2019'
Group by ID;
Maybe something like this? It creates a key of ID|Shipping Date and then finds the max when grouped by ID and joins it to the initial load.
data:
NoConcatenate
Load *,
ID & '|'& [Shipping Date] as Max_Ship_Date_Key;
load * inline [
ID, Shipping Date
1, 4/3/2018 3:19:00 AM
1, 10/22/2018 12:46:00 AM
1, 10/22/2018 12:46:00 AM
1, 2/11/2019 12:37:00 AM
1, 2/11/2019 12:37:00 AM
1, 7/23/2019 12:41:00 AM
1, 7/23/2019 12:42:00 AM
1, 1/7/2020 1:21:00 AM
1, 1/7/2020 1:21:00 AM
1, 2/11/2020 2:58:00 AM
1, 2/11/2020 2:59:00 AM
2, 10/20/2017 5:54:00 AM
2, 1/17/2018 4:12:00 AM
2, 12/7/2019 11:37:00 PM
3, 10/22/2018 12:45:00 AM
3, 10/22/2018 12:46:00 AM
3, 2/11/2019 12:36:00 AM
3, 2/11/2019 12:37:00 AM
3, 6/7/2019 7:37:00 AM
3, 1/7/2020 1:21:00 AM
3, 1/28/2020 4:26:00 AM
];
data2:
Left Join(data)
load
ID & '|'& FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_Date_Key,
FirstSortedValue([Shipping Date],-[Shipping Date]) as Max_Ship_date
Resident data
Where [Shipping Date] <= '12/1/2019'
Group by ID;
Can you help me with Set Analysis instead load statement?
Try this...
Aggr(Max({<[Shipping Date]={"<=$(='12/1/2019')"}>}[Shipping Date]),ID)
Not knowing your data model, I would personally make that date a variable where it can be changed by the user. If you go this route the expression would be
Aggr(Max({<[Shipping Date]={"<=$(=vDate)"}>}[Shipping Date]),ID) where vDate is your user-input variable.
HTH,
John
Did John's last post get you what you needed? If so, do not forget to come back and properly close out your thread by using the Accept as Solution button on that post of John's that got you the info you needed, as this will give him credit and let other Members know it did work.
Here is a Design Blog on Set Analysis basics that has some additional links at the bottom and after that I am including the base URL for the Design Blog area in case you want to do some further searching, as I am sure some of those posts may help further.
https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344
https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822
https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog
Actually found a couple direct posts that may be most helpful followed by the base URL.
Regards,
Brett