topic Re: Max(date) as flag in load in QlikView

Max(date) as flag in load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a simple data with a date field. I want to create a new field, "Maxflag" in the table where if the Date is the max(Date) group by ID then Maxflag=Y.

I don't want to just load the maximum rows. I want to load everything but with a field ("Maxflag") that identifies which row is has the max date for each ID.

DATA:

LOAD * INLINE [

ID, Date

1, 1/1/2018

1, 3/1/2018

2, 9/1/2019

2, 3/1/2018

3, 5/1/2018

3, 4/1/2017

];

Intended table should be:

ID	Date	Maxflag
1	1/1/2018
1	3/1/2018	Y
2	9/1/2019	Y
2	3/1/2018
3	5/1/2018	Y
3	4/1/2017

Re: Max(date) as flag in load

passionate — Sun, 15 Apr 2018 16:34:14 GMT

PFA, Solution

Re: Max(date) as flag in load

sasiparupudi1 — Sun, 15 Apr 2018 17:00:40 GMT

Another Solution..

Data:

LOAD ID,

Date

FROM

[https://community.qlik.com/thread/298279]

(html, codepage is 1252, embedded labels, table is @1);

Left join

LOAD ID,

Max(Date) as Date,

'Y' as MaxFlag

FROM

[https://community.qlik.com/thread/298279]

(html, codepage is 1252, embedded labels, table is @1)

Group by ID;

Re: Max(date) as flag in load

MarcoWedel — Sun, 15 Apr 2018 19:29:21 GMT

Hi,

another solution could be:

tabDATATemp:
LOAD ID,
    Date
FROM [https://community.qlik.com/thread/298279] (html, codepage is 1252, embedded labels, table is @1);

tabDATA:
LOAD *,
    If(ID<>Previous(ID),'Y') as MaxFlag
Resident tabDATATemp
Order By ID, Date desc;

DROP Table tabDATATemp;

hope this helps

regards

Marco