topic Re: Pivot: extract only certain orders in QlikView

Pivot: extract only certain orders

qlikviewaf — Wed, 12 May 2021 07:32:30 GMT

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

Re: Pivot: extract only certain orders

MayilVahanan — Wed, 12 May 2021 08:07:23 GMT

Hi @qlikviewaf

Try like below

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

Re: Pivot: extract only certain orders

qlikviewaf — Wed, 12 May 2021 09:47:26 GMT

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

Re: Pivot: extract only certain orders

MayilVahanan — Wed, 12 May 2021 09:52:09 GMT

HI @qlikviewaf

Try like below

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

Re: Pivot: extract only certain orders

qlikviewaf — Wed, 12 May 2021 12:46:46 GMT

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!!!

Re: Pivot: extract only certain orders

MayilVahanan — Thu, 13 May 2021 01:14:25 GMT

Hi @qlikviewaf

I think, its hep you ..

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

Re: Pivot: extract only certain orders

Saravanan_Desingh — Thu, 13 May 2021 01:49:13 GMT

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;

Re: Pivot: extract only certain orders

Saravanan_Desingh — Thu, 13 May 2021 01:49:44 GMT

Output: