Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

How do I get max date in the script

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

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
Dept
NUM;
FROM Company_Table

Left Join (MaxOrder)

LOAD OrderKey,
DATERECEIVED,
DATEOFORDER,
CoKey,

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

FROM Order_Table

8 Replies
Highlighted

Re: How do I get max date in the script

May be try?

Load

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Master III
Master III

Re: How do I get max date in the script

Hi Stephanie,

next step in script

Left Join (MaxOrder)

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

Resident MaxOrder Group By CoKey;

Regards,

Antonio

Highlighted
Master III
Master III

Re: How do I get max date in the script

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
Dept
NUM;
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:


Highlighted
Contributor II
Contributor II

Re: How do I get max date in the script

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?

Highlighted
Master III
Master III

Re: How do I get max date in the script

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;

Highlighted
Master III
Master III

Re: How do I get max date in the script

May be this?

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
Dept
NUM;
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;


Highlighted

Re: How do I get max date in the script

Hi

Try like this

MaxOrder:

LOAD CoKey,
CoTYPE,
CoNAME,
DeptNAME,
Dept
NUM;
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;

Highlighted
Master
Master

Re: How do I get max date in the script

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;