topic Re: Add max date field in QlikView

Add max date field

Anonymous — Mon, 21 Aug 2017 09:19:21 GMT

Hi all,

I am trying to create an additional field (through my load script) called "Latest Date", which gives me the max LoginDate for each ID. So if max LoginDate for ID 2168 is 14-02-2017, then this date should appear in every row where ID = 2168.

How can I achieve this?

Thanks in advance!

Re: Add max date field

sunny_talwar — Mon, 21 Aug 2017 15:30:20 GMT

May be this

Fact:

LOAD ID,

Date,

OtherFields

FROM ...

Left Join (Fact)

LOAD ID,

Max(Date) as [Latest Date]

Resident Fact

Group By ID;

Re: Add max date field

Anil_Babu_Samineni — Mon, 21 Aug 2017 15:31:37 GMT

May be this?

Left join to fact

Load *, Max(Date) as MaxDate

Resident Fact

Group By <Non Aggr Dimensions>;

Re: Add max date field

its_anandrjs — Tue, 22 Aug 2017 06:24:42 GMT

Here i need to ask if you have LoginDate in your table it self then try with

MainTable:

Load

ID,

LoginDate,

...

From Location;

Left Join(MainTable)

Load

ID,

DATE(MAX(LoginDate)) as MaxLoginDate

Resident MainTable

Group By ID;

Re: Add max date field

Anonymous — Tue, 22 Aug 2017 11:28:39 GMT

Thanks guys! All three answers were very helpful

In the end Anand came closest to the solution which I could use directly in my code, so I will mark his answer as correct.

Thanks again.