Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have a problem with loading two excel files into a same table. i have seen some tutorials but still have problem because the structure of the two file are identic (pivot table) but contains two different KPI "Budget" and "Réalisé".
Please find the attachement to see the structure of the files.
the structure oh the table i want to create is:
-ID
-Attribut
- Date
- Réalisé
-Budget
I have successfuly load one file with the code bellow:
Treso:
CrossTable(Mois, Données)
LOAD REALISE,
[012015],
[022015],
[032015],
[042015],
[052015],
[062015],
[072015],
[082015],
[092015],
[102015],
[112015],
[122015]
FROM
(ooxml, embedded labels, header is 3 lines, table is [Tréso]);
QUALIFY*;
TF_TRESORERIE_REALISE:
LOAD
RowNo() as treso_ID,
REALISE,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as DATE_ID,
Données as Données
Resident Treso;
DROP Table Treso;
UNQUALIFY*;
What is the issue here?
Thank you for your reply i have seen that before. My case is different because the excel files are a pivot and they do not contain the same KPI. ( Budget VS Réalisé).
The first excel file contains :
- ATTRIBUT
- DATE
- BUDGET
The second excel file contains :
- ATTRIBUT
- DATE
- REALISE
and id like to have :
- ATTRIBUT
- DATE
- BUDGET
- REALISE
the excel files are a pivot and they do not contain the same KPI. ( Budget VS Réalisé).
The first excel file contains :
- ATTRIBUT
- DATE
- BUDGET
The second excel file contains :
- ATTRIBUT
- DATE
- REALISE
and id like to have :
- ID
- ATTRIBUT
- DATE
- BUDGET
- REALISE
i don't know how to load both of them since there is two different KPI and i have pivot table . maybe should I change the format of the excel files ??
try this
table_total:
load * inline [REALISE, Moise, Données];
Treso:
CrossTable(Mois, Données)
LOAD REALISE,
[012015],
[022015],
[032015],
[042015],
[052015],
[062015],
[072015],
[082015],
[092015],
[102015],
[112015],
[122015]
FROM
[..\Trésorerie2015.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [Tréso]);
// exit script;
//QUALIFY*;
//TF_TRESORERIE_REALISE:
concatenate(table_total)
LOAD
RowNo() as treso_ID,
REALISE,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as DATE_ID,
Données as Données
Resident Treso;
DROP Table Treso;
//
Treso:
CrossTable(Mois, Données)
LOAD Budget ,
[012016],
[022016],
[032016],
[042016],
[052016],
[062016],
[072016],
[082016],
[092016],
[102016],
[112016],
[122016]
FROM
[..\Trésorerie2016.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [Tréso]);
// exit script;
//QUALIFY*;
//TF_TRESORERIE_REALISE:
concatenate(table_total)
LOAD
RowNo() as treso_ID,
Budget,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as DATE_ID,
Données as Données
Resident Treso;
DROP Table Treso;
see this
Treso_Realise:
CrossTable(Mois, Données)
LOAD REALISE,
[012015],
[022015],
[032015],
[042015],
[052015],
[062015],
[072015],
[082015],
[092015],
[102015],
[112015],
[122015]
FROM
(ooxml, embedded labels, header is 3 lines, table is [Tréso]);
Treso_Budget:
CrossTable(Mois, Données)
LOAD Budget ,
[012016],
[022016],
[032016],
[042016],
[052016],
[062016],
[072016],
[082016],
[092016],
[102016],
[112016],
[122016]
FROM
TF_TRESORERIE_REALISE:
LOAD
RowNo() as treso_ID,
REALISE as Measure,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as DATE_ID,
'Realise' as MeasureFlag
Données as Données
Resident Treso_Realise;
DROP Table Treso_Realise;
concatenate(TF_TRESORERIE_REALISE)
LOAD
RowNo() as treso_ID,
Budget as Measure,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as DATE_ID,
'Budget' as MeasureFlag
Données as Données
Resident Treso_Budget;
DROP Table Treso_Budget;
Expression:
Budget -Sum({<MeasureFlag={'Budget'}>}Measure)
Realise - Sum({<MeasureFlag={'Realise'}>}Measure)
thank you all for your replies.
i try the code below:
Treso_Budget:
CrossTable(Mois, Données)
LOAD Budget,
[012016],
[022016],
[032016],
[042016],
[052016],
[062016],
[072016],
[082016],
[092016],
[102016],
[112016],
[122016]
FROM
(ooxml, embedded labels, header is 3 lines, table is Tréso);
Treso_Realise:
CrossTable(Mois, Données)
LOAD REALISE,
[012015],
[022015],
[032015],
[042015],
[052015],
[062015],
[072015],
[082015],
[092015],
[102015],
[112015],
[122015]
FROM
(ooxml, embedded labels, header is 3 lines, table is Tréso);
TF_TRESORERIE:
LOAD
RowNo() as treso_ID,
REALISE as Attribut,
//REALISE,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as TF_TRESORERIE.DATE_ID,
'Realise' as MeasureFlag,
Données as REALISE
Resident Treso_Realise;
DROP Table Treso_Realise;
concatenate(TF_TRESORERIE)
LOAD
RowNo() as treso_ID,
Budget as Attribut,
//Budget,
Date(Floor(Date(MonthEnd(Date#(Mois,'MMYYYY')),'DD/MM/YYYY'))) as TF_TRESORERIE.DATE_ID,
'Budget' as MeasureFlag,
Données as Budget
Resident Treso_Budget;
DROP Table Treso_Budget;
i have just change REALISE as Attribut because REALISE is the title of the dimensions if you see the excel file and the measure id Données.
this give a table like this:
this is what i want thank you, but the question is that if every time the end user gives me an excel file to add it , i should concatenate it with the others??
because (they give an excel file every semester)