Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be try?
Load
FirstSortedValue(OrderDate, -CoKey) Group By ......
Hi Stephanie,
next step in script
Left Join (MaxOrder)
LOAD CoKey,Date(Max(OrderDate)) as MaxOrderDate
Resident MaxOrder Group By CoKey;
Regards,
Antonio
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:
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?
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;
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;
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;
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;