topic Re: How do I get max date in the script in QlikView

How do I get max date in the script

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

What I would like to do is find the max order date for each CoKey.

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
DeptNUM;
FROM Company_Table

Left Join (MaxOrder)

LOAD OrderKey,
DATERECEIVED,
DATEOFORDER,
CoKey,
DATE(DATEOFORDER, 'MM/DD/YYYY') as OrderDate;

FROM Order_Table

Re: How do I get max date in the script

Anil_Babu_Samineni — Fri, 04 Aug 2017 15:15:08 GMT

May be try?

Load

FirstSortedValue(OrderDate, -CoKey) Group By ......

Re: How do I get max date in the script

antoniotiman — Fri, 04 Aug 2017 15:39:30 GMT

Hi Stephanie,

next step in script

Left Join (MaxOrder)

LOAD CoKey,Date(Max(OrderDate)) as MaxOrderDate

Resident MaxOrder Group By CoKey;

Regards,

Antonio

Re: How do I get max date in the script

sasiparupudi1 — Mon, 07 Aug 2017 01:17:54 GMT

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
DeptNUM;
FROM Company_Table

Left Join (MaxOrder)

LOAD OrderKey,
DATERECEIVED,
DATEOFORDER,
CoKey,
DATE(DATEOFORDER, 'MM/DD/YYYY') as OrderDate;

FROM Order_Table

Left Join (MaxOrder)

LOAD
CoKey,
Max (DATEOFORDER) as MaxOrderDate

FROM Order_Table

Group by CoKey:

Re: How do I get max date in the script

Anonymous — Tue, 08 Aug 2017 15:26:35 GMT

Sorry everyone, should've been more specific. I would only like to pull the order line from those max dates. So instead of getting millions of records I only get several thousand. Can this be done?

Re: How do I get max date in the script

antoniotiman — Tue, 08 Aug 2017 16:19:19 GMT

next step in script

Inner Join (MaxOrder) //Instead of Left in my previous answer

LOAD CoKey,Date(Max(OrderDate)) as MaxOrderDate

Resident MaxOrder Group By CoKey;

Re: How do I get max date in the script

sasiparupudi1 — Wed, 09 Aug 2017 02:50:05 GMT

May be this?

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
DeptNUM;
FROM Company_Table

Inner Join (MaxOrder)

LOAD Max (OrderKey) as OrderKey,
Max (DATERECEIVED) as DATERECEIVED
CoKey,
DATE(Max (DATEOFORDER), 'MM/DD/YYYY') as OrderDate;

FROM Order_Table

Gtoup by Cokey;

Re: How do I get max date in the script

MayilVahanan — Wed, 09 Aug 2017 10:10:30 GMT

Try like this

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
DeptNUM;
FROM Company_Table

Left Join (MaxOrder)

LOAD OrderKey,
DATERECEIVED,
DATEOFORDER,
CoKey,
DATE(DATEOFORDER, 'MM/DD/YYYY') as OrderDate;

FROM Order_Table

inner join(MaxOrder)

Load CoKey, max(OrderDate) As OrderDate Resident MaxOrder Group by CoKey;

Re: How do I get max date in the script

effinty2112 — Wed, 09 Aug 2017 12:13:33 GMT

Hi Stephanie.

This should do it:

LastOrders:

LOAD OrderKey,

DATERECEIVED,

DATEOFORDER,

CoKey,

DATE(DATEOFORDER, 'MM/DD/YYYY') as OrderDate;

FROM Order_Table

Inner Join

CoKey,

Max(OrderDate) as OrderDate

Resident LastOrders Group by CoKey;