Pivot: extract only certain orders

[qlikviewaf]

Hi All,

i have the following data-set with Order Number - Item and Amount.

Region	OrderNumber	Item	Amount
Europe	1	AAAA	10
Europe	1	BBBB	20
Europe	1	ZZZZ	5
Europe	1	CCCC	30
Europe	2	DDDD	10
Europe	2	CCCC	20

Im trying to build a Pivot Table that extract only Order containing Item Number = AAAA , like the table below.

I'm kind of lost - anyone has an idea how to do this? Thanks!!

Region	Amount
Europe	65

[MayilVahanan]

Hi @qlikviewaf 

Try like below

=Sum({<OrderNumber={$(=Only({<Item={'AAAA'}>}OrderNumber))}>}Amount)

[qlikviewaf]

unfortunately Is not working if in the dataset there are more than 1 order containing the item "aaaa"

[MayilVahanan]

HI @qlikviewaf 

Try like below

=Sum({<OrderNumber={$(=chr(39)&Concat(Distinct{<Item={'AAAA'}>}OrderNumber ,chr(39)&','&chr(39))&chr(39))}>}Amount)

[qlikviewaf]

Seems working - how i can integrate the same into my script? in order to have a column where value is Y if the order contain that item number? 

Thanks!!!

[MayilVahanan]

Hi @qlikviewaf 

I think, its hep you ..

https://community.qlik.com/t5/QlikView-App-Dev/Create-Flag/td-p/1807323

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    Region, OrderNumber, Item, Amount
    Europe, 1, AAAA, 10
    Europe, 1, BBBB, 20
    Europe, 1, ZZZZ, 5
    Europe, 1, CCCC, 30
    Europe, 2, DDDD, 10
    Europe, 2, CCCC, 20
];

Left Join(tab1)
LOAD Region, OrderNumber, If(Index(Concat(DISTINCT Item),'AAAA')>0,1) As Flag
Resident tab1
Group By Region, OrderNumber;

Left Join(tab1)
LOAD Region, OrderNumber, Sum(Amount) As Sum
Resident tab1
Where Flag=1
Group By Region, OrderNumber;

[Saravanan_Desingh]

Output: