Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

load different excel files into the same table

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

7 Replies
Kushal_Chawda

What is the issue here?

Not applicable
Author

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

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

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)

Not applicable
Author

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:

treso.png

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)