Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Products sold together on prescription

I have the following data in my table:

Screen Shot 2015-02-19 at 19.46.21.png

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

9 Replies
Highlighted
MVP
MVP

try the attachment

1.jpg

Highlighted

Massimo could you post your expression,please?

I'm working on PE.

Thank you

Highlighted
MVP
MVP

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

Highlighted
Specialist
Specialist

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

Kindly elaborate further

Regards

Highlighted
Specialist
Specialist

P in set analysis means all possible values

Highlighted
Specialist
Specialist

Nick many thanks for the clarification, most appreciated.

Chris

Highlighted
Specialist
Specialist

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:

Screen Shot 2015-02-19 at 23.03.06.png

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

Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

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