LOADing only records with the largest value

[richard_chilvers]

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

[sunny_talwar]

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

[swuehl]

Maybe like

LOAD *, CustomerID as CusterExist

RESIDENT YourFactTable

WHERE NOT EXISTS(CustomerExist, CustomerID)

ORDER BY SalesAmount Desc

DROP TABLE YourFactTable;

[Anonymous]

Do you only want the purchased item description for the largest purchase value record for each Customer ID?

[tresesco]

May be like:

Load

          ID,

          FirstSortedValue(Field1, - PurchaseValue) as Field1Max,

          FirstSortedValue(Field2, - PurchaseValue) as Field2Max,

          ....

From <> Group By ID;

[sunny_talwar · Accepted solution]

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;

[richard_chilvers]

No, I would like ALL the fields for each customer record with the highest value

[richard_chilvers]

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 !