topic Re: Re: associated rows in QlikView

associated rows

mambi — Sat, 22 Feb 2014 13:57:25 GMT

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 .

orderID	product	QTY
1	book	5
1	pen	7
1	ruler	2
2	paper	10
2	pencil	4
3	rubber	1

any idea how to do it ?

Re: associated rows

maxgro — Sat, 22 Feb 2014 15:13:21 GMT

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)

Re: associated rows

mambi — Sat, 22 Feb 2014 15:32:50 GMT

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

Re: Re: associated rows

sundarakumar — Sat, 22 Feb 2014 18:46:21 GMT

Hi Mambi,

PFA

Hope this helps

-Sundar

Re: associated rows

mambi — Sat, 22 Feb 2014 20:57:44 GMT

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.

Re: associated rows

agilos_mla — Sat, 22 Feb 2014 21:37:12 GMT

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

Re: associated rows

mambi — Sat, 22 Feb 2014 21:54:00 GMT

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 ?

Re: associated rows

maxgro — Sat, 22 Feb 2014 23:24:50 GMT

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