7 Replies Latest reply: Dec 21, 2015 10:17 AM by bruno bertels RSS

    Help with left join

    bruno bertels

      Hi Qlikers and Sensers

       

      I use this script to get 2 tables

       

      first is an response from a client survey (Questionnaire)

       

      second is details on full clients (base)

       

      key between those 2 tables is Code_Bouton.

       

      Of course I have less responses than total client and i just want to left join those 2 table in the meaning of drop client data for those where i have no responses to the survey to gain memory space.

       

      Could someone add the line script to left join or keep join  those 2 tables please.

       

      I don't find an easy "How to do" for newbies and dummies in the forum and unfortunately for me, even if the help site of qlik sense is very helpful , the examples adding seem to never corresponding to my requierment.

       

      Questionnaire:

      LOAD [clic contact] as Code_Bouton,

          [Q-1 De manière globale, comment évaluez-vous  la QUALITE de nos biens et services] as Q1,

          [Q-2  De manière générale, comment évaluez-vous le rapport qualité-prix des produits/services ?] as Q2,

          [Q-3 Dernière question : Quelle est la probabilité pour vous de recommander XXX à un proche (ami, collègue, etc...)] as Q3

      FROM [lib://NPS SATISFACTION/*.xlsx]

      (ooxml, embedded labels, table is [Réponses au formulaire 1]);

       

       

      base:

      LOAD [Mois Rdv],

          [Semaine Rdv],

          [Type Appel],

          [Date prise Rdv],

          [Date],

          [Conseiller],

          [Zone du conseiller],

          [Organisation],

          [Assistante],

          [Site Assistante],

          [Rdv Pris Par],

          [Entreprise],

          [Code Bouton] as Code_Bouton,

          [Téléphone],

          [Mobile organisme],

          [Departement],

          [Ville],

          [Score],

          [Statut organisme au moment de l'appel],

          [Type contrat],

          [Mois souhaitée contrat]

      FROM [lib://NPS SATISFACTION/*.xlsx]

      (ooxml, embedded labels, table is base);

       

      thanks in advance

       

      Bruno

        • Re: Help with left join
          Sangram Reddy

          Hi Bruno,

          Try this:

           

          Questionnaire:

          LOAD [clic contact] as Code_Bouton,

              [Q-1 De manière globale, comment évaluez-vous  la QUALITE de nos biens et services] as Q1,

              [Q-2  De manière générale, comment évaluez-vous le rapport qualité-prix des produits/services ?] as Q2,

              [Q-3 Dernière question : Quelle est la probabilité pour vous de recommander XXX à un proche (ami, collègue, etc...)] as Q3

          FROM [lib://NPS SATISFACTION/*.xlsx]

          (ooxml, embedded labels, table is [Réponses au formulaire 1]);

           

          Left Join([Questionnaire])

          base:

          LOAD [Mois Rdv],

              [Semaine Rdv],

              [Type Appel],

              [Date prise Rdv],

              [Date],

              [Conseiller],

              [Zone du conseiller],

              [Organisation],

              [Assistante],

              [Site Assistante],

              [Rdv Pris Par],

              [Entreprise],

              [Code Bouton] as Code_Bouton,

              [Téléphone],

              [Mobile organisme],

              [Departement],

              [Ville],

              [Score],

              [Statut organisme au moment de l'appel],

              [Type contrat],

              [Mois souhaitée contrat]

          FROM [lib://NPS SATISFACTION/*.xlsx]

          (ooxml, embedded labels, table is base);

          • Re: Help with left join
            Gilles Courtin

            Hi,

             

            Try this :

             

            Questionnaire:

            LOAD [clic contact] as Code_Bouton,

                 ----.

                 ----

            FROM [lib://NPS SATISFACTION/*.xlsx]

            (ooxml, embedded labels, table is [Réponses au formulaire 1]);

             

            base:

            Left Keep (Questionnaire)

            Load [Mois Rdv],

                ----

                ----

            FROM [lib://NPS SATISFACTION/*.xlsx]

            (ooxml, embedded labels, table is base);

            Regards

            Gilles

            • Re: Help with left join
              bruno bertels

              Hi

               

              At the en it's working with this script below.

               

              Other solutions maked issues : synthetic key or not reducing data to only customer with a survey

               

              In this script :

               

              I loaded first

              questionnaire :

              then

              base :

               

              i Loaded a third base named Quest: from resident Questionnaire with some added dimention

              I drooped table questionnaire

              and then :

               

              left join (Quest)

              Quest1:

              load

              *

              resident base;

              drop table base;

               

              ---------------------------------------------------------------------------------------------------------

              Questionnaire:

              LOAD

              [clic contact] as Code_Bouton,

                 .........

              FROM [lib://NPS SATISFACTION/*.xlsx]

              (ooxml, embedded labels, table is [Réponses au formulaire 1]);

               

               

              base:

              LOAD

              ....

              [Code Bouton] as Code_Bouton,

              .....

              FROM [lib://NPS SATISFACTION/*.xlsx]

              (ooxml, embedded labels, table is base);

               

               

              Quest:

              load

              Code_Bouton,

              .......

                

              resident Questionnaire;

              drop table Questionnaire;

                

               

              left join (Quest)

              Quest1:

              load

              *

              resident base;

              drop table base;