Transpose Excel Data on QlikView

[Anonymous]

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.

[sunny_talwar]

Use CrossTable Load: The Crosstable Load

[sunny_talwar]

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]);

[sunny_talwar · Accepted solution]

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;

[Anonymous]

hi,

use crossetable to lead your data.

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

[Anonymous]

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.

[sunny_talwar]

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;

[sunny_talwar]

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;

[Anonymous]

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.

[sunny_talwar]

Like this?