Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
mambi
Contributor III

associated rows

Hi everyone,

from the table below : i want to get ( in a pivot table ) only the associated products with the selected one i.e : if in my list i select 'book' -> my pivot table should display only pen and ruler with their respective quantities .

orderIDproductQTY
1book5
1pen7
1ruler2
2paper10
2pencil4
3rubber1

any idea how to do it ?

Tags (2)
1 Solution

Accepted Solutions
agilos_mla
Contributor III

Re: associated rows

Try this

sum({$ <orderID=P(orderID), product=E(product)>} QTY)

Should take all orderID associated to the product(s) selected but discarding the production selection (=E syntax)

Michael

7 Replies
MVP
MVP

Re: associated rows

if I understand you want only product of the same order,  try with (see attachment)

2 dimension          orderID

                              product

expression          sum({$ <product=,  orderID=P(orderID)>} QTY)

108325.jpg

mambi
Contributor III

Re: associated rows

i want the product of the same order - the selected one

sundarakumar
Valued Contributor II

Re: Re: associated rows

Hi Mambi,

PFA

Hope this helps

-Sundar

mambi
Contributor III

Re: associated rows

not really what i expected because i.e : if i select 'book' in the list box i want to get in the pivot table  only pen and ruler cause they have the same orderID in other way i want to get the associated values with my selection.

agilos_mla
Contributor III

Re: associated rows

Try this

sum({$ <orderID=P(orderID), product=E(product)>} QTY)

Should take all orderID associated to the product(s) selected but discarding the production selection (=E syntax)

Michael

mambi
Contributor III

Re: associated rows

Thanks a lot sir,

i got the same result now by using this formula :

sum({1 <product-={'$(=GetFieldSelections(product))'},  orderID=P(orderID)>} QTY)

but i'm a little confusing because if i use sum({$ <product ... instead of sum({1 <product... it's won't work any explanation ?

MVP
MVP

Re: associated rows

sum({$ <product=-{$(=concat(product, ','))},  orderID=P(orderID)>} QTY)

$ represents the records of the current selection; 1 (not needed in above expression) represents the full set of all the records in the application

product=-{$(=concat(product, ','))}   represents all products except the selected products (=-   not   -=   they have different meaning)

P() represents the element set of possible values

Community Browser