Products sold together on prescription

[Anonymous]

I have the following data in my table:

In this table, products sold together have the same Script Number (First Column in table).  I want to be able to select a product name from a list box filter to identify all products sold together with the selected product.  Say for example i select product name "NIZORAL(KETOCONAZOLE) I want to be able to list other products sold on the same Script Number which is '41' namely GRESEOFULVIN 125mg/5ml SUSP and FLUCLOXACILLIN 125MG/5ML.  How can i accomplish this in QlikView?

Thanking you in advance.

Regards

Chris

[maxgro]

try the attachment

[robert_mika]

Massimo could you post your expression,please?

I'm working on PE.

Thank you

[maxgro]

sum({$ <[Product Name]=,[Script Number]=P([Script Number])>} [Medical Aid Amount])

[Anonymous]

Many thanks Massimo.  I do not understand the script where it says [Script Number] = P

Kindly elaborate further

Regards

[NickHoff]

P in set analysis means all possible values

[Anonymous]

Nick many thanks for the clarification, most appreciated.

Chris

[Anonymous]

Hi Massimo

Many thanks it certainly helps.  However, you can have the same 'Product Name' appearing in another prescription.  I am getting the following from some 2 million records or so:

This now gives multiple prescriptions instead of confining to one only.  Is there a way out of this?

[NickHoff]

AGGR(sum({$ <[Product Name]=,[Script Number]=P([Script Number])>} [Medical Aid Amount]), [Drug Code])

[Anonymous]

Hi Nick

Many thanks for your input.  Aggregating based on the [Drug Code] will result in the table only showing one record, i.e. the selected drug only.

Regards

Chris