
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
firstsortedvalue, aggr and valuelist
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:
- Item
- ValueList('SO', 'IN, 'Rcv1', 'Rcv2)
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
FirstSortedValue( DISTINCT aggr( NODISTINCT SUM(Table2.AMT), Table1.Item, Table1.Relation, Table2.ID3), Table2.Num,2)
Adding DISTINCT did the trick.
