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]

Hi Philippe,

You could use the following code (in the data load editor)

PREVCA:

Load

     PREVCA_MOIS & '_' & PREVCA_ANNEE as MOIS_ANNE,

     PREVCA_NCL,

CATTC

FROM [2nd ExcelL file];

left join (PREVCA)

Load

     NCL as PREVCA_NCL,

     FILIERES,

FROM [1st Excel file];

HEUR:

Load

     HEUR_MOIS & '_' & HEUR_ANNEE as MOIS_ANNE,

     HEUR_NCL,

     HEURES

FROM [3rd Excel file];

left join (HEUR)

Load

     NCL as HEUR_NCL,

     FILIERES,

FROM [1st Excel file];

PREVFP:

Load

     PREVFP_MOIS & '_' & PREVFP_ANNEE as MOIS_ANNE,

     PREVFP_NCL,

     CATTC

FROM [4th Excel file];

left join (HEUR)

Load

     NCL as PREVFP_NCL,

     FILIERES,

FROM [1st Excel file];

Sectors:

Load

     FILIERES,

     NCL_LIB

FROM [1st Excel file];

Hope it helps.

Felipe.

[philgood34]

Hi Felipe

thank's , your reponse seems to be very helpful... I'll try tomorrow (working at the moment ...)

is there a solution with bubbles to personalize the association as your script ?

it'will be nice to me, cause as a beginner i don't use the editor at the time being ...

regards

Philippe

[philgood34]

However, i'll try your script ... i promise !

[philgood34]

Failed 😞

first i watched for the automatic script by QLIK SENSE

Then, inspirated by your script i replaced LOAD [HEUR_ANNEE] with LOAD [HEUR_MOIS & '_' & HEUR_ANNEE as MOIS_ANNE]

and i deleted [HEUR_MOIS]

that for each file, like this

and here the error message :

it seems that the new field has to be created ?

have you a idea ?

see you

Philippe

[rahulpawarb]

Hello Philippe,

Adding [ & ] brackets to entire statement will consider this as a field present in base file; however we are creating it a calculated field using two different fields from base file. Therefore, Avoid brackets or apply to every single field from the base file (refer below expression).

Replace the [HEUR_MOIS & '_' & HEUR_ANNEE as MOIS_ANNE] with [HEUR_MOIS] & '_' & [HEUR_ANNEE] as MOIS_ANNE

If you still have problems with this then please share the application. This will help us to provide you expected changes.

Regards!

Rahul Pawar

[philgood34]

Hi Rahul

thank's you so much

i prefer let you see with my files ...

beginning with script edition is rather difficult without basic  training 😞

so, please find my files joined and you 'll see:

in [SAMPLE ASSOCIATE_DATES] : my essay with the automatic script of QLIK SENSE

and in [ASSOCIATE DATES]  : my essay with the felip'advices in the script editor

Regards

Philippe

.

[philgood34]

got it !

i had an issue with the path of my files. it' ok now

thank' a lot Felip & Rahul.

an another subsidiaire question if you please ...

i wrote in the script editor ... perfect !

but now i can't anymore edit my tables with the qlik sense intuitive editor that is so fine for a beginner like me ...

My question is : is it possible to create the composed key wroten as [HEUR_MOIS] & '_' & [HEUR_ANNEE] as MOIS_ANNE with the "bubbles association's editor" and a personnalised association  as shown lower (it is another application to demonstrate) ?

Howhever, thank you for your answers

Philippe

[felipedl]

Hi Philippe,

Qlik Sense evaluates the data you have and make the associations in the data manager (the part you demonstrated above) and would have to create the extra fields so that the associations are made by the MOIS_ANNE field created for each table,

Something like: PREVFP_MOIS & '_' & PREVFP_ANNEE

The following script makes mostly the same thing, but creates another table 'LinkTable' to join the information by a key that is created in each individual table.

The code below gives the following table associations

NCL:

LOAD

    NCL,

    FILIERES,

    NCL_LIB

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

(ooxml, embedded labels, table is NOMENCLATURE);

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

LinkTable:

Load

Key,

MOIS_ANNE,

    PREVCA_NCL as NCL

Resident PREVCA;

Concatenate(LinkTable)

Load

Key,

MOIS_ANNE,

    HEUR_NCL as NCL

Resident HEUR;

Concatenate(LinkTable)

Load

Key,

MOIS_ANNE,

    PREVFP_NCL as NCL

Resident PREVFP;

drop field MOIS_ANNE from PREVCA,PREVFP,HEUR;

drop field PREVFP_NCL from PREVFP;

drop field HEUR_NCL from HEUR;

drop field PREVCA_NCL from PREVCA;

[philgood34]

hi Felip and thank's again

So, if i understand, between your initial script (with left join ) and the lastest (with link table), we have rather the same result but in the second case we have a additionnal link table ... more efficient ?

Meanwhile i successed with this script

...

[NOMENCLATURE]:

LOAD

[NCL],

[FILIERES],

[NCL_LIB],

APPLYMAP('__cityKey2GeoPoint', APPLYMAP('__cityName2Key', LOWER([NCL_LIB])), '-') AS [NOMENCLATURE.NCL_LIB_GeoInfo]

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

(ooxml, embedded labels, table is NOMENCLATURE);

[HEUR_R]:

LOAD [HEUR_MOIS]&'_'&[HEUR_ANNEE] as [MOIS_ANNE],

  [HEUR_ANNEE],

    [HEUR_MOIS],

[HEUR_NCL],

[HEURES]

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

(ooxml, embedded labels, table is HEUR_R);

left join (HEUR_R)

Load

     NCL as HEUR_NCL,

     FILIERES,

APPLYMAP('__cityKey2GeoPoint', APPLYMAP('__cityName2Key', LOWER([NCL_LIB])), '-') AS [NOMENCLATURE.NCL_LIB_GeoInfo]

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

(ooxml, embedded labels, table is NOMENCLATURE);

[CA_PREV]:

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

left join (CA_PREV)

Load

     NCL as PREVCA_NCL,

     FILIERES,

APPLYMAP('__cityKey2GeoPoint', APPLYMAP('__cityName2Key', LOWER([NCL_LIB])), '-') AS [NOMENCLATURE.NCL_LIB_GeoInfo]

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

(ooxml, embedded labels, table is NOMENCLATURE);

[HEUR_PREV]:

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

left join (HEUR_PREV)

Load

     NCL as PREVFP_NCL,

     FILIERES,

APPLYMAP('__cityKey2GeoPoint', APPLYMAP('__cityName2Key', LOWER([NCL_LIB])), '-') AS [NOMENCLATURE.NCL_LIB_GeoInfo]

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

(ooxml, embedded labels, table is NOMENCLATURE);

TAG FIELD [NCL_LIB] WITH '$geoname', '$relates_NOMENCLATURE.NCL_LIB_GeoInfo' ;

TAG FIELD [NOMENCLATURE.NCL_LIB_GeoInfo] WITH '$geopoint', '$hidden', '$relates_NCL_LIB' ;

DROP TABLES __cityAliasesBase, __cityGeoBase;

wich is the best script ?