Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
richard_chilvers
Valued Contributor

LOADing only records with the largest value

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

1 Solution

Accepted Solutions

Re: LOADing only records with the largest value

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;

Capture.PNG

7 Replies

Re: LOADing only records with the largest value

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

MVP
MVP

Re: LOADing only records with the largest value

Maybe like

LOAD *, CustomerID as CusterExist

RESIDENT YourFactTable

WHERE NOT EXISTS(CustomerExist, CustomerID)

ORDER BY SalesAmount Desc

DROP TABLE YourFactTable;

Re: LOADing only records with the largest value

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

MVP
MVP

Re: LOADing only records with the largest value

May be like:

Load

          ID,

          FirstSortedValue(Field1, - PurchaseValue) as Field1Max,

          FirstSortedValue(Field2, - PurchaseValue) as Field2Max,

          ....

From <> Group By ID;

Re: LOADing only records with the largest value

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;

Capture.PNG

richard_chilvers
Valued Contributor

Re: LOADing only records with the largest value

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

richard_chilvers
Valued Contributor

Re: LOADing only records with the largest 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 !