Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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 |
check this -
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!
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-
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".
You can add a calculated dimension as - =If(Match(Currency, 'USD'), 'OTHERS', Currency)
or you can use the same expression in script as well.