topic Re: Associated Tables With Dates in Connectivity & Data Prep

Associated Tables With Dates

philgood34 — Wed, 16 Aug 2017 15:04:18 GMT

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

Re: Associated Tables With Dates

felipedl — Wed, 16 Aug 2017 21:01:04 GMT

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.

Re: Associated Tables With Dates

philgood34 — Wed, 16 Aug 2017 21:15:51 GMT

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

Re: Associated Tables With Dates

philgood34 — Wed, 16 Aug 2017 21:17:46 GMT

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

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 08:36:46 GMT

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

Re: Associated Tables With Dates

rahulpawarb — Thu, 17 Aug 2017 09:21:04 GMT

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

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 09:32:57 GMT

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

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 11:58:53 GMT

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

Re: Associated Tables With Dates

felipedl — Thu, 17 Aug 2017 12:44:22 GMT

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;

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 13:54:01 GMT

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 ?

Re: Associated Tables With Dates

felipedl — Thu, 17 Aug 2017 14:30:37 GMT

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.

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 14:35:45 GMT

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 ?

Re: Associated Tables With Dates

felipedl — Thu, 17 Aug 2017 14:41:34 GMT

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)

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

Load

sum(a) as A,

sum(b) as B

From [whatever];

Load

A+B as C

Resident x;

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 14:47:39 GMT

very clear,

Understood !

Vou tentar ...

Viva Portugal !

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 15:58:38 GMT

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 ?

Re: Associated Tables With Dates

philgood34 — Thu, 17 Aug 2017 17:25:05 GMT

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)