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.

[Anonymous]

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

Sébastien

[sunny_talwar]

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.

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

Thanks for this !

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

Okay, thanks a lot !