Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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]))
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.
Hi,
If you want to add new indicator
It should be created using inline only.
Ok, but i want to automatize that without enter into the script
Do you have a solution please ?
Sébastien
No idea on that brother
will find it and let u know.
Regards,
Joshua.
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.
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;
Hi,
PFA