15 Replies Latest reply: Aug 17, 2017 1:25 PM by Philippe BONNIN RSS

    Associated Tables With Dates

    Philippe BONNIN

      hi

       

       

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

       

      I have 5 tables

       

      NCL.png

      CA.png

       

       

      HEURES.png

      HEURES_PREV.png

       

       

      CA_PREV.png

      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 :-(

      ASSOCIATE.png

       

      and that :-((

      MODELES.png

      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
          Felip Drechsler

          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
              Philippe BONNIN

              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 ?

              bubbles.png

              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
                Philippe BONNIN

                Failed :-(

                 

                first i watched for the automatic script by QLIK SENSE

                SCRIPT_AUTO.png

                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

                SCRIPT_IMPROVED.png

                 

                 

                and here the error message :

                 

                ERROR.png

                it seems that the new field has to be created ?

                 

                have you a idea ?

                 

                see you

                 

                Philippe

                  • Re: Associated Tables With Dates
                    Rahul Pawar

                    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
                        Philippe BONNIN

                        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
                            Felip Drechsler

                            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

                            Sample.png

                             

                            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
                                Philippe BONNIN

                                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
                                  Philippe BONNIN

                                  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
                                      Felip Drechsler

                                      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;

                                        • Re: Associated Tables With Dates
                                          Philippe BONNIN

                                          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

                                           

                                          felip.png

                                           

                                          In mine (and Rahul i think)

                                           

                                          we have

                                           

                                          philippe.png

                                           

                                          What do you think about ?

                                            • Re: Associated Tables With Dates
                                              Philippe BONNIN

                                              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)

                                    • Re: Associated Tables With Dates
                                      Philippe BONNIN

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


                                      bubbles.png


                                      Howhever, thank you for your answers


                                      Philippe

                                        • Re: Associated Tables With Dates
                                          Felip Drechsler

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

                                           

                                          Sample.png

                                           

                                          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.