Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am loading records of customer purchases.
For each Customer ID, I want to select only one record selected by the largest purchase value. Other fields (eg. purchased item description) will vary between records for the same customer.
What is the most efficient way of doing this in the script ?
Thanks
Look at this sample script:
Table:
LOAD * Inline [
Customer, Dim1, Purchase_Value
A, XDS, 10
A, FSD, 30
A, DO, 43
B, AS, 44
B, ED, 34
];
Right Join (Table)
LOAD Customer,
Max(Purchase_Value) as Purchase_Value
Resident Table
Group By Customer;
May be like this:
Table:
LOAD CustomerID,
OtherFields,
Purchase_Value
FROM Source;
Right Join (Table)
LOAD CustomerID,
Max(Purchase_Value) as Purchase_Value
Resident Table
Group By CustomerID;
UPDATE: Forgot to add the Group By statement initially. Added it as an update
Maybe like
LOAD *, CustomerID as CusterExist
RESIDENT YourFactTable
WHERE NOT EXISTS(CustomerExist, CustomerID)
ORDER BY SalesAmount Desc
DROP TABLE YourFactTable;
Do you only want the purchased item description for the largest purchase value record for each Customer ID?
May be like:
Load
ID,
FirstSortedValue(Field1, - PurchaseValue) as Field1Max,
FirstSortedValue(Field2, - PurchaseValue) as Field2Max,
....
From <> Group By ID;
Look at this sample script:
Table:
LOAD * Inline [
Customer, Dim1, Purchase_Value
A, XDS, 10
A, FSD, 30
A, DO, 43
B, AS, 44
B, ED, 34
];
Right Join (Table)
LOAD Customer,
Max(Purchase_Value) as Purchase_Value
Resident Table
Group By Customer;
No, I would like ALL the fields for each customer record with the highest value
Hi
Thanks for your prompt response.
I have a problem with pesky null values, but I think this eventually is going to be a neat solution !