Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
cocalero
Creator
Creator

Transpose Excel Data on QlikView

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

19 Replies
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

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;

View solution in original post

sbobbyraj
Creator III
Creator III

hi,
use crossetable to lead your data.
you can use the guide to load your table with crosstable.
cocalero
Creator
Creator
Author

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;

cocalero
Creator
Creator
Author

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?

Capture.PNG