topic Re: Max Record for Max Date in QlikView

Max Record for Max Date

qlikwiz123 — Sat, 16 Nov 2024 04:23:14 GMT

I have the sample data as below.

Age	A.Date	B.Date	Unique ID
1	2019-02-01 12:02:23	2/20/2019	ABC55
2	2019-02-04 12:01:48	2/20/2019	ABC55
3	2019-02-05 12:02:12	2/20/2019	ABC55
4	2019-02-06 12:01:45	2/20/2019	ABC55
5	2019-02-07 12:02:00	2/20/2019	ABC55
6	2019-02-08 12:02:26	2/20/2019	ABC55
7	2019-02-11 12:01:54	2/20/2019	ABC55

I need to create a new field in the script where it contains the Age for the maximum A.Date column.

For ABC55, the New Age field should contain 7 since the Maximum A.Date Timestamp is 2019-02-11 12:01:54

Re: Max Record for Max Date

tincholiver — Wed, 20 Feb 2019 17:02:42 GMT

Table:
LOAD * Inline
[Age, A.Date, B.Date, Unique ID
1, 2019-02-01, 12:02:23, 2/20/2019, ABC55
2, 2019-02-04, 12:01:48, 2/20/2019, ABC55
3, 2019-02-05, 12:02:12, 2/20/2019, ABC55
4, 2019-02-06, 12:01:45, 2/20/2019, ABC55
5, 2019-02-07, 12:02:00, 2/20/2019, ABC55
6, 2019-02-08, 12:02:26, 2/20/2019, ABC55
7, 2019-02-11, 12:01:54, 2/20/2019, ABC55
];

Left Join
Temp_Date:
LOAD
[Unique ID],
max(A.Date) as Max_Date
Resident Table Group by [Unique ID];

Re: Max Record for Max Date

qlikwiz123 — Wed, 20 Feb 2019 17:30:32 GMT

I need to create a New Age field for the Maximum A.Date column and this one only adds Max(A.Date) to the existing table

Re: Max Record for Max Date

vishsaggi — Wed, 20 Feb 2019 17:50:47 GMT

Try this?

T1:
LOAD Age,
A.Date,
B.Date,
[Unique ID]
FROM
[https://community.qlik.com/t5/New-to-QlikView/Max-Record-for-Max-Date/m-p/1547024#M370379]
(html, utf8, embedded labels, table is @1);

Left Join(T1)
LOAD [Unique ID],
Date(Max(A.Date)) AS MaxDate
Resident T1
Group By [Unique ID];

Left Join(T1)
T2:
LOAD Age AS NewAge
Resident T1
WHERE A.Date = MaxDate;

Re: Max Record for Max Date

andrescc — Thu, 21 Feb 2019 04:46:28 GMT

Hi,
Try this:

Table:
LOAD * Inline
[Age, A.Date, B.Date, Unique ID
1, 2019-02-01, 12:02:23, 2/20/2019, ABC55
2, 2019-02-04, 12:01:48, 2/20/2019, ABC55
3, 2019-02-05, 12:02:12, 2/20/2019, ABC55
4, 2019-02-06, 12:01:45, 2/20/2019, ABC55
5, 2019-02-07, 12:02:00, 2/20/2019, ABC55
6, 2019-02-08, 12:02:26, 2/20/2019, ABC55
7, 2019-02-11, 12:01:54, 2/20/2019, ABC55
];

Left Join
Temp_Date:
LOAD
[Unique ID],
LastValue(Age) as New_field_age
Resident Table
Group by [Unique ID]
Order By A.Date asc;

Regards,
ACC