Help with left join

[brunobertels]

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

[reddy-s]

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

[brunobertels]

Hi Sangram

When adding this

here is what i obtain :

the 2 tables are well join But no data in it :

should i try with a mapping load rather than a keep or left join ?

Bruno

[reddy-s]

Hi Bruno,

Mapping allows only 2 fields in the mapping table and will not fit your purpose. Left join is the right suggestion here.

But, Just Curious! Why do you want to join the tables when QlikSense does it for you as you have renamed the fields?

[brunobertels]

Well

I could let my script as it is , it's working. But i just need to extract aditionnal information from client database to join them to client survey as i have for example 1000 clients but only 100 survey. So i don't need to keep other client information from client who did not respond to my survey.

So i thought a left join was the best practice for that case.

perhaps a where clause could solve this ?

[#6]

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

• Help with left join

Regards

Gilles

[jagan]

Hi Bruno,

Then use Left Keep(), this will filter out the clients that do not have survey.

Regards,

jagan.

[brunobertels · Accepted solution]

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;