topic Re: Getting records with only max date in QlikView

Getting records with only max date

mhassinger — Thu, 26 Jul 2018 04:44:51 GMT

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.

Re: Getting records with only max date

ankit777 — Thu, 26 Jul 2018 05:16:46 GMT

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

Re: Getting records with only max date

tresB — Thu, 26 Jul 2018 05:42:26 GMT

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;

Re: Getting records with only max date

MindaugasBacius — Thu, 26 Jul 2018 05:46:08 GMT

What about:

Load

Max(Created Date) as [Created Date],

Max(Key1) as Key1,

Key2

Group by Key2

;

Re: Getting records with only max date

MarcoWedel — Thu, 26 Jul 2018 21:39:06 GMT

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

Re: Getting records with only max date

MarcoWedel — Thu, 26 Jul 2018 21:52:55 GMT

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