Improve Structure of Import Excel and Diplay on Pivot Table

[Anonymous]

Hello,

I would like to improve my Data Structure because currently i used freeze set expression and it is not steady if new indicators will appear.

My data are taken from Excel File with a loop thanks to the folder address:

For each File in filelist('Z:\*.xlsx*')

LOAD Folder,

     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

[$(File)]

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

NEXT

Here all data are stock in the table [Data].

Now i would like to create a Table, named for example 'Indicators', which regroup all field which start with [PNL Notion/ .... ] automatically in the import and create a pivot table which display the data (PNL Notion) according to the choice of this field (Indicators).

Currently i have setup this:

LOAD * INLINE [

    AIR_Indicators

    Final Result Acc

    EDM

    Net Trade Result

    Reallo CVA/DVA

    Retro Cooperation 2

    Sales Credit Newedge

    SLS Final Result

    SMTD Impact

    SR IN TRD

    CFP-CMM

    Others

];

The expression looks like this for each notions:

if(Currency = 'EUR', SUM([Pnl_Notion/Final Result Acc/EUR]),

if(Currency = 'USD', SUM([Pnl_Notion/Final Result Acc/USD])))

Currently the expression are not in link with the table Indicators, but i would like to do this but not like because I think it is too steady:

if(Indicators = 'Final Result Acc' ,if(Currency = 'EUR', SUM([Pnl_Notion/Final Result Acc/EUR]),

if(Currency = 'USD', SUM([Pnl_Notion/Final Result Acc/USD]))))

The notion are displayed one by one because i setup a group:

How can i simplify that please ?

Thanks you in advance for your help,

Sébastien.

[ajsjoshua]

Hi,

LOAD * INLINE [

   id,  Indicators

   1, AIR_Indicators

   2, Final Result Acc

   3, EDM

   4, Net Trade Result

    5,Reallo CVA/DVA

   6, Retro Cooperation 2

    7,Sales Credit Newedge

    8,SLS Final Result

    9,SMTD Impact

    10,SR IN TRD

    11,CFP-CMM

    12,Others

];

if(GetFieldSelections(Indicators)= 'AIR_Indicators' ,

SUM([Pnl_Notion/Final Result Acc/EUR]),

if(GetFieldSelections(Indicators)= 'Final Result Acc' ,

,if(Currency = 'EUR', SUM([Pnl_Notion/Final Result Acc/EUR]),

if(Currency = 'USD', SUM([Pnl_Notion/Final Result Acc/USD]))

[Anonymous]

Thanks for your response, but how can i update this table if a new indicators such as [Pnl_Notion/Net Result Acc/EUR] will create ? Can i create this table in the Excel Data loop importation ?

Sébastien.

[ajsjoshua]

Hi,

If you want to add new indicator

It should be created using inline only.

[Anonymous]

Ok, but i want to automatize that without enter into the script

Do you have a solution please ?

Sébastien

[ajsjoshua]

No idea on that brother

will find it and let u know.

Regards,

Joshua.

[Anonymous]

I will check if i can change the data structure because if we have one field 'Indicator' in LOAD with all Indicators  [Pnl_Notion/....] , it could be more easier:

For each File in filelist('Z:\*.xlsx*')

LOAD Folder,

     Pc,

    Indicators,

FROM

[$(File)]

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

NEXT

With that, can i create a load with the distinct indicators in the field 'Indicators' ?

Sébastien.

[ajsjoshua]

Try it

tablename:

LOAD

    Indicators,

     FirstValue(Folder) AS Folder,

     FirstValue(Pc) AS Pc

FROM

[$(File)]

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

GROUP BY Indicators;

[ajsjoshua]

Hi,

Try with Trim functions

Trim Field function - Extract number from text | Qlik Community

[ajsjoshua]

Hi,

PFA