Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mambi
Creator III
Creator 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 ?

1 Solution

Accepted Solutions
agilos_mla
Partner - Creator III
Partner - Creator III

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

View solution in original post

7 Replies
maxgro
MVP
MVP

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
Creator III
Creator III
Author

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

sundarakumar
Specialist II
Specialist II

Hi Mambi,

PFA

Hope this helps

-Sundar

mambi
Creator III
Creator III
Author

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
Partner - Creator III
Partner - Creator III

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
Creator III
Creator III
Author

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 ?

maxgro
MVP
MVP

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