Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So I've got data that looks something like:
Key1 | Key2 | Created Date | Record Type |
---|---|---|---|
1 | 1 | 1/1/2018 | A |
2 | 1 | 1/2/2018 | B |
3 | 2 | 1/1/2018 | B |
4 | 2 | 1/2/2018 | D |
I want to get the records that have the max date, grouped by key 2, like:
Key1 | Key2 | Created Date | Record Type |
---|---|---|---|
2 | 1 | 1/2/2018 | B |
4 | 2 | 1/2/2018 | D |
It's easy enough to do
Load Max(Crated Date), Key2
Group by Key2
But that doesn't get me Key1 and Record Type.
Hi
After getting max created date and key2, you can join remaining columns from original table based on combination of key2 and created date.
Try like:
Load
Max([Created Date]) as [Created Date],
Key2,
FirstSortedValue(Key1, - [Created Date]) as Key1,
FirstSortedValue([Record Type] ,- [Created Date]) as [Record Type]
Group by Key2;
What about:
Load
Max(Created Date) as [Created Date],
Max(Key1) as Key1,
Key2
Group by Key2
;
Hi,
one solution might be:
table1:
LOAD * FROM [https://community.qlik.com/thread/309017] (html, codepage is 1252, embedded labels, table is @1);
Right Join
LOAD Key2,
Max([Created Date]) as [Created Date]
Resident table1
Group By Key2;
hope this helps
regards
Marco
another solution would be:
table1:
LOAD * FROM [https://community.qlik.com/thread/309017] (html, codepage is 1252, embedded labels, table is @1);
table2:
LOAD *,
Key2 as Key2Temp
Resident table1
Where not Exists (Key2Temp, Key2)
Order By [Created Date] desc;
DROP Table table1;
DROP Field Key2Temp;
hope this might help as well
regards
Marco