Associated Tables With Dates

[philgood34]

hi

To explain my issue, i created simples Excel 's tables

I have 5 tables

As you guess,I want to associate the 4 last tables with a composed key MOIS_ANNEE (1_2016;2_2016.....),

then associate this 4 tables with the first one which contain the NCL code.

I tried with connexions between every tables ....

here is the result 😞

and that :-((

There are errors in the application that i join .

I hope some help ... Unfortunently i don't unterstand the basic principle, and i 'm blocked ...

Thank's

[felipedl]

Yes, its possible.

You have to go to the data manager (loading the tables through this method also) select the table and editing it (the pencil icon).

Afterwards, you click "Add Field" on the right top corner and then "Calculated Field". There, you can create the concatenated field you want in the "Expression" box .

You should see something like this (mine is in portuguese).

As to adding the LinkTable, instead of multiplying the NCL field in all tables, you can set it as a dimension to the LinkTable, not duplicating the data in all original tables.

Felipe.

[philgood34]

Another question after examinate your script

as shown in the pictures below, several fields are loaded 2 times ...

PREVCA:

Load

MOIS_ANNE & '_' & PREVCA_NCL as Key,

    PREVCA_NCL,

MOIS_ANNE,

    PREVCA_CATTC;

Load

PREVCA_MOIS & '_' & PREVCA_ANNEE as MOIS_ANNE,

PREVCA_NCL,

PREVCA_CATTC

FROM [lib://Files/PREVCA.xlsx]

(ooxml, embedded labels, table is CA_PREV);

HEUR:

Load

MOIS_ANNE & '_' & HEUR_NCL as Key,

    HEUR_NCL,

MOIS_ANNE,

    HEURES;

Load

HEUR_MOIS & '_' & HEUR_ANNEE as MOIS_ANNE,

HEUR_NCL,

HEURES

FROM [lib://Files/HEUR.xlsx]

(ooxml, embedded labels, table is HEUR_R);

PREVFP:

Load

MOIS_ANNE & '_' & PREVFP_NCL as Key,

    PREVFP_NCL,

MOIS_ANNE,

    PREVFP_HEUR;

Load

PREVFP_MOIS & '_' & PREVFP_ANNEE as MOIS_ANNE,

PREVFP_NCL,

    PREVFP_HEUR

FROM [lib://Files/PREVFP.xlsx]

(ooxml, embedded labels, table is HEUR_PREV);

is it necessary, especially for PREVCA_CATTC, HEURES & PREV_HEUR ?

[felipedl]

This is called a precedent load. It is used so that you can do some kind of treatment on the fields, withou having to load two distinct tables.

Example (let say i want to sum some calculations)

x

Load

     A+B as C, // note that here, the fields are already named as stated bellow, being called as A and B instead of the individual sums.

     A, // can be omited, but wont show in the final table

     B; // can be omited, but wont show in the final table  

Load

     sum(a) as A,

     sum(b) as B

From [whatever]

Could be done as

x:

Load

     sum(a) as A,

     sum(b) as B

From [whatever];

y:

Load

     A+B as C

Resident x;

[philgood34]

very clear,

Understood !

Vou tentar ...

Viva Portugal !

[philgood34]

little bit more complex ... i'll meditate ..

can you send me your .qvf if you please ? it would be a good example to be keep ...

Have you use the same excel's files i sended initialy ?  Because i have a doubt with semantics

I explain

in your script we have

In mine (and Rahul i think)

we have

What do you think about ?

[philgood34 · Accepted solution]

Before close these great discussion, i let my script with the correct semantics (in connection with the excel's files let at the beginning.

[NOMENCLATURE]:

LOAD

[NCL],

[FILIERES],

[NCL_LIB]

FROM [lib://ASSOCIATION_DATES/NCL.xlsx]

(ooxml, embedded labels, table is NOMENCLATURE);

CA_PREV:

Load

MOIS_ANNE & '_' & PREVCA_NCL as Key,

    PREVCA_NCL,

    MOIS_ANNE,

    PREVCA_CATTC;

Load

PREVCA_MOIS & '_' & PREVCA_ANNEE as MOIS_ANNE,

PREVCA_NCL,

PREVCA_CATTC

FROM [lib://ASSOCIATION_DATES/PREVCA.xlsx]

(ooxml, embedded labels, table is CA_PREV);

HEUR_R:

Load

MOIS_ANNE & '_' & HEUR_NCL as Key,

    HEUR_NCL,

    MOIS_ANNE,

    HEURES;

Load

HEUR_MOIS & '_' & HEUR_ANNEE as MOIS_ANNE,

HEUR_NCL,

HEURES

FROM [lib://ASSOCIATION_DATES/HEUR.xlsx]

(ooxml, embedded labels, table is HEUR_R);

HEUR_PREV:

Load

MOIS_ANNE & '_' & PREVFP_NCL as Key,

    PREVFP_NCL,

    MOIS_ANNE,

    PREVFP_HEUR;

Load

PREVFP_MOIS & '_' & PREVFP_ANNEE as MOIS_ANNE,

PREVFP_NCL,

    PREVFP_HEUR

FROM [lib://ASSOCIATION_DATES/PREVFP.xlsx]

(ooxml, embedded labels, table is HEUR_PREV);

LinkTable:

Load

Key,

MOIS_ANNE,

    PREVCA_NCL as NCL

Resident CA_PREV;

Concatenate(LinkTable)

Load

Key,

MOIS_ANNE,

    HEUR_NCL as NCL

Resident HEUR_R;

Concatenate(LinkTable)

Load

Key,

MOIS_ANNE,

    PREVFP_NCL as NCL

Resident HEUR_PREV;

drop field MOIS_ANNE from CA_PREV,HEUR_PREV,HEUR_R;

drop field PREVFP_NCL from HEUR_PREV;

drop field HEUR_NCL from HEUR_R;

drop field PREVCA_NCL from CA_PREV;

Hourra Felipe !

(well, for the "precedent load", i'll comme back)