firstsortedvalue, aggr and valuelist

jduluc12 — Mon, 15 Mar 2021 08:51:17 GMT

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?

Re: firstsortedvalue, aggr and valuelist

jduluc12 — Tue, 16 Mar 2021 05:46:41 GMT

FirstSortedValue( DISTINCT aggr( NODISTINCT SUM(Table2.AMT), Table1.Item, Table1.Relation, Table2.ID3), Table2.Num,2)

Adding DISTINCT did the trick.

topic Re: firstsortedvalue, aggr and valuelist in QlikView

firstsortedvalue, aggr and valuelist

Re: firstsortedvalue, aggr and valuelist