associated rows

[mambi]

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 ?

[maxgro]

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)

[mambi]

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

[sundarakumar]

Hi Mambi,

PFA

Hope this helps

-Sundar

[mambi]

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 · Accepted solution]

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]

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]

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