Getting records with only max date

[mhassinger]

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.

[ankit777]

Hi

After getting max created date and key2, you can join remaining columns from original table based on combination of key2 and created date.

[tresesco]

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;

[MindaugasBacius]

What about:

Load

    Max(Created Date) as [Created Date],

    Max(Key1) as Key1,

    Key2

Group by Key2

;

[MarcoWedel]

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

[MarcoWedel]

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