topic Re: Pivot Table: Display subset in QlikView

Pivot Table: Display subset

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need help with displaying my data in a certain way. Any help is appreciated.

I wish to show it like this in a pivot table format in qlikview:

PCS_Name	PCS	Currency	Count(ISIN)
ANZBank	ANZ1	AUD	2
		CAD	0
		NZD	1
	ANZ2	AUD	1
		CAD	0
		NZD	1
Citi Bank	CITI1	AUD	0
		NZD	0
		OTHERS	1

Right now i have only Table 1 and Table 2 as shown below. I need to massage the data on Currency column on Table 1 so that i can join Table 1 and 2 where Currency = ISIN_Curency and PCS = PCS.

Logic on Table 1 Currency:

Display only AUD, NZD, CAD and everything else (eg USD) fall into OTHERS including OTHERS

Below is a sample data from my 400k row excel sheet.

Table 1

PCS_Name	PCS	Currency
ANZ Bank	ANZ1	AUD
ANZ Bank	ANZ1	NZD
ANZ Bank	ANZ1	CAD
ANZ Bank	ANZ2	AUD
ANZ Bank	ANZ2	NZD
ANZ Bank	ANZ2	CAD
Citi Bank	CITI1	USD
Citi Bank	CITI1	AUD
Citi Bank	CITI1	NZD
Citi Bank	CITI1	OTHERS

Table 2

ISIN	ISIN_Currency	PCS
ISIN1	AUD	ANZ1
ISIN2	NZD	ANZ1
ISIN3	AUD	ANZ2
ISIN4	NZD	ANZ2
ISIN5	AUD	ANZ1
ISIN6	USD	CITI1

Re: Pivot Table: Display subset

neelamsaroha157 — Tue, 04 Sep 2018 17:33:56 GMT

check this -

Re: Pivot Table: Display subset

Anonymous — Thu, 06 Sep 2018 09:38:56 GMT

Hi @Neelam

Thanks for the reply. In this case how do you group USD or any other currencies like ZAR or SGD under OTHERS?

can you please show the script here? I'm using the personal edition Qlikview.

Thanks!

Re: Pivot Table: Display subset

neelamsaroha157 — Thu, 06 Sep 2018 17:29:07 GMT

Script -

tab2:

LOAD * INLINE [

ISIN ISIN_Currency PCS

ISIN1 AUD ANZ1

ISIN3 AUD ANZ2

ISIN5 AUD ANZ1

ISIN2 NZD ANZ1

ISIN4 NZD ANZ2

ISIN6 USD CITI1

](delimiter is '\t');

RENAME Field PCS to PCS2;

tab1:

LOAD * INLINE [

PCS_Name PCS Currency

ANZ Bank ANZ1 AUD

ANZ Bank ANZ1 NZD

ANZ Bank ANZ1 CAD

ANZ Bank ANZ2 AUD

ANZ Bank ANZ2 NZD

ANZ Bank ANZ2 CAD

Citi Bank CITI1 USD

Citi Bank CITI1 AUD

Citi Bank CITI1 NZD

Citi Bank CITI1 OTHERS

] (delimiter is '\t');

NoConcatenate

LOAD *, PCS & '-' & Currency as Key

Resident tab1; DROP Table tab1;

LOAD *, PCS2 & '-' & ISIN_Currency as Key

Resident tab2;DROP Table tab2;

Pivot table-

Re: Pivot Table: Display subset

Anonymous — Fri, 07 Sep 2018 01:26:32 GMT

Hi Neelam

Under CITI1, i wish to have USD be within OTHERS. is that possible?

If you look at my original post, the first table.

PCS_Name	PCS	Currency	Count (ISIN)
Citi Bank	CITI1	AUD	0
		NZD	0
		OTHERS	1

in my data files, i have alot of other currencies which i wish to display under the name "OTHERS".

Re: Pivot Table: Display subset

neelamsaroha157 — Fri, 07 Sep 2018 15:57:18 GMT

You can add a calculated dimension as - =If(Match(Currency, 'USD'), 'OTHERS', Currency)

or you can use the same expression in script as well.