topic Re: Transpose Excel Data on QlikView in QlikView

Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 08:52:35 GMT

Hello,

Please find attached a sample of my data set.

I import my data from an Excel document but the structure of the data set is not optimal for me, that is why i would like to transpose the data into three columns such as PC, Notions and Val.
-> Notions will be the list of all column on my data set which start with "Pnl_Notion/...."

How can i do that into QlikView without modify the Excel file please ?

Thanks you in advance for your help.

Sébastien.

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 09:09:30 GMT

Use CrossTable Load: The Crosstable Load

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 09:11:16 GMT

Check this:

Table:

CrossTable(Type, Value)

LOAD Pc,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD]

FROM

[Data Test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 09:14:09 GMT

Or this if you want to get rid of all those rows where Value = 0

Table:

CrossTable(Type, Value)

LOAD Pc,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD]

FROM

[Data Test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where Value <> 0;

DROP Table Table;

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 09:21:30 GMT

hi,

use crossetable to lead your data.

you can use the guide to load your table with crosstable.

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 09:31:28 GMT

Thanks a lot ! it is exactly that .

Just one more question, how can i keep just the name of the Indicator, so the string between the "/" for example:

[Pnl_Notion/Reallo CVA / DVA/EUR] ==> Reallo CVA/DVA

Then i would like to create a table, named Currency, which will select the indicators if it is "EUR" or "USD"

Thanks you in advance for your help,

Sébastien.

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 09:38:39 GMT

Try this:

Table:

CrossTable(Type, Value)

LOAD Pc,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD]

FROM

[Data Test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);

FinalTable:

NoConcatenate

LOAD Pc,

Left(Mid(Type, Index(Type, '/') + 1), Len(Mid(Type, Index(Type, '/') + 1)) - 4) as Type,

Value

Resident Table

Where Value <> 0;

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 09:43:08 GMT

My bad, did not read the Currency requirement

Table:

CrossTable(Type, Value)

LOAD Pc,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD]

FROM

[Data Test.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR]);

FinalTable:

NoConcatenate

LOAD Pc,

Left(Mid(Type, Index(Type, '/') + 1), Len(Mid(Type, Index(Type, '/') + 1)) - 4) as Type,

SubField(Type, '/', -1) as Currency,

Value

Resident Table

Where Value <> 0;

DROP Table Table;

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 12:55:57 GMT

Thanks a lot but here i have two identical Notions for example Final Result Acc.

How can i select the currency EUR or USD ?

Thanks in advance,

Sébastien.

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 12:58:16 GMT

Like this?

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 12:59:52 GMT

Yes exactly, but i don't need to display the currency into the Pivot Table.

Sébastien

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 13:02:04 GMT

What you are seeing is not a pivot table. It is a table box. And to create a pivot table without Currency, you can just not add it as a dimension.

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 14:45:37 GMT

Thanks a lot for your response .

But now i need to do this for several file in a folder with other field, my script here:

For each File in filelist('F:\ACFI-MAR\CTY\MAR\Macro BPA\Extraction PnL\*.xlsx*')

LOAD Folder,

Sbu,

SbuLabel,

Pc,

Pclabel,

Ggop,

GgopLabel,

Gop,

GopLabel,

Ptf,

PtfLabel,

Source,

key,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD],

LEFT(MID('$(File)',59,70),10) as 'AIR Import Data'

FROM

[$(File)]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR])

WHERE Pc <> 'Toto';

NEXT

I wrote LEFT(MID('$(File)',59,70),10) as 'AIR Import Data' because i want to keep the title with the date of each file.

How can i adapt this script with yours please ?

Sébastien.

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 14:50:17 GMT

Can you share of the file name here? Does that include AIR Import Data in it?

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 14:52:47 GMT

For example it could be this:
Pnl by place 2015-02-27.xlsx

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 14:56:01 GMT

So I guess you want to call it [Air Import Data for all your files, regardless of which file it comes from? Try this:

Date#(Left(Mid('$(File)',59,70),10), 'YYYY-MM-DD') as [AIR Import Data]

Used Date#() to make sure the date is read as date by QlikView

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 14:57:57 GMT

Thanks for this !

Then can i use the loop 'For Each' here with the Cross Table and the other field please ?

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 15:08:07 GMT

Because i tried this but the green part doen't work 😕 :

For each File in filelist('F:\ACFI-MAR\CTY\MAR\Macro BPA\Extraction PnL\*.xlsx*')

Table:

CrossTable(Type, Value)

LOAD Folder,

Sbu,

SbuLabel,

Pc,

Pclabel,

Ggop,

GgopLabel,

Gop,

GopLabel,

Ptf,

PtfLabel,

Source,

key,

[Pnl_Notion/CFP-CMM/EUR],

[Pnl_Notion/CFP-CMM/USD],

[Pnl_Notion/E.D.M./EUR],

[Pnl_Notion/E.D.M./USD],

[Pnl_Notion/Final Result Acc/EUR],

[Pnl_Notion/Final Result Acc/USD],

[Pnl_Notion/Net Trade Result/EUR],

[Pnl_Notion/Net Trade Result/USD],

[Pnl_Notion/Reallo CVA / DVA/EUR],

[Pnl_Notion/Reallo CVA / DVA/USD],

[Pnl_Notion/Retro Cooperation 2/EUR],

[Pnl_Notion/Retro Cooperation 2/USD],

[Pnl_Notion/SLS Final Result/EUR],

[Pnl_Notion/SLS Final Result/USD],

[Pnl_Notion/SMTD Impact/EUR],

[Pnl_Notion/SMTD Impact/USD],

[Pnl_Notion/SR IN TRD/EUR],

[Pnl_Notion/SR IN TRD/USD],

[Pnl_Notion/Sales Credits Newedge/EUR],

[Pnl_Notion/Sales Credits Newedge/USD],

Date#(LEFT(MID('$(File)',59,70),10), 'YYYY-MM-DD') as [AIR Import Data]

FROM

[$(File)]

(ooxml, embedded labels, header is 1 lines, table is [Data AIR])

WHERE Pc <> 'TOTO';

NEXT

FinalTable:

NoConcatenate

LOAD Folder,

Sbu,

SbuLabel,

Pc,

Pclabel,

Ggop,

GgopLabel,

Gop,

GopLabel,

Ptf,

PtfLabel,

Source,

key,

LEFT(MID('$(File)',59,70),10) as 'AIR Import Data',

Left(Mid(Type, Index(Type, '/') + 1), Len(Mid(Type, Index(Type, '/') + 1)) - 4) as Type,

SubField(Type, '/', -1) as Currency,

Value

Resident Table

Where Value <> 0;

DROP Table Table;

Thank you in advance for your help,

Sébastien.

Re: Transpose Excel Data on QlikView

sunny_talwar — Wed, 18 May 2016 15:12:32 GMT

I have couple of back to back meetings. Will look at it in a bit

Re: Transpose Excel Data on QlikView

Anonymous — Wed, 18 May 2016 15:14:48 GMT

Okay, thanks a lot !