How do I get max date in the script

[Anonymous]

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

[Anil_Babu_Samineni]

May be try?

Load

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

[antoniotiman]

Hi Stephanie,

next step in script

Left Join (MaxOrder)

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

Resident MaxOrder Group By CoKey;

Regards,

Antonio

[sasiparupudi1]

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:

[Anonymous]

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?

[antoniotiman]

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;

[sasiparupudi1]

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;

[MayilVahanan]

Hi

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;

[effinty2112]

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;