topic Re: max date in load script in QlikView

max date in load script

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

I need Max date for the Purchase date, but it should groupby only CustID. I'm invalid expression when I use the below script.

T1:

LOAD Distinct

Department,

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID;

DROP Table Temp;

Re: max date in load script

prma7799 — Fri, 02 Feb 2018 12:11:31 GMT

Have you tried this

T1:

LOAD Distinct

Department,

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID,Department;

DROP Table Temp;

Re: max date in load script

karan_kn — Fri, 02 Feb 2018 12:16:26 GMT

Thanks for the response, but I need to Group By only CustID

Re: max date in load script

Peter_Cammaert — Fri, 02 Feb 2018 12:17:45 GMT

Then remove all other fields, because for every unique CustomerID you will get a single Max Date, but possibly multiple Item_Names and they won't fit in a single row. Like:

T1:

LOAD CustID,

Max(Date(Purchase_Date, 'MM/DD/YYYY')) AS Max_Date

RESIDENT Temp

GROUP BY CustID;

The idea is that all column names in the Load that do not appear in the GROUP BY clause, should be embedded in an aggregation function call, like Max(), Min(), Only(), FirstSortedValue(), Sum(), Avg() etc.

Re: max date in load script

prma7799 — Fri, 02 Feb 2018 12:39:47 GMT

Try this

T1:

LOAD

CustID,

Item_Name,

Max(Date(Purchase_Date,'MM/DD/YYYY')) as Max_Date

Resident Temp Group by CustID;

DROP Table Temp;