load different excel files into the same table

[#1]

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*;

[Chanty4u]

chk dis

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[Kushal_Chawda]

What is the issue here?

[#4]

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

[#5]

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 ??

[florentina_doga]

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;

[Kushal_Chawda]

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)

[#8]

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)