Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables
Table_1:
Item | ID1 | AMT1 | ID2 | AMT2 | Relation |
Item_1 | ID1_1 | 2 | ID2_1 | 3 | 1 |
Item_2 | ID1_2 | 20 | ID2_2 | 56 | 2 |
Table_2:
ID2 | AMT | ID3 | Num |
ID2_1 | 2 | ID3_1 | 1 |
ID2_1 | 20 | ID3_2 | 2 |
ID2_2 | 50 | ID3_3 | 3 |
the desired pivot table is
Item | Relation | Type | AMT | |
Item_1 | 1 | SO | 2 | |
IN | 4 | |||
Rcv1 | 2 | |||
Rcv2 | 20 | |||
Item_2 | 2 | SO | 20 | |
IN | 56 | |||
Rcv1 | 50 |
Dimension:
Expression:
if(ValueList('SO', 'IN', 'Rcv1', 'Rcv2') = 'SO', only(AMT1),
if (ValueList('SO', 'IN', 'Rcv1', 'Rcv2') = 'IN', only(AMT2),
if (ValueList('SO', 'IN', 'Rcv1', 'Rcv2') = 'Rcv1',
FirstSortedValue( aggr( NODISTINCT SUM(Table2.AMT), Table1.Item, Table1.Relation, Table2.ID3), Table2.Num,1),
FirstSortedValue( aggr( NODISTINCT SUM(Table2.AMT), Table1.Item, Table1.Relation, Table2.ID3), Table2.Num,2),
)))
the issue is the firstsortedvalue (). It returns nothing.
if I just use the aggr without the firstsortedvalue then I get values but I can not separate the Rcv1 from Rcv2.
what am I missing here?
FirstSortedValue( DISTINCT aggr( NODISTINCT SUM(Table2.AMT), Table1.Item, Table1.Relation, Table2.ID3), Table2.Num,2)
Adding DISTINCT did the trick.