Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
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
However, i'll try your script ... i promise !
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
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
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
.
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
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;
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 ?