1 Reply Latest reply: Oct 23, 2018 7:25 AM by patrice salem RSS

    inner / outer join in Qlik desktop

    patrice salem

      Hello

       

      In order to run individual target follow-up I have the following tables :

      Image 1842.jpg

       

      The table Targets contain the targets of all salespeople.

      Let say ,the sales tables contain targets of 1 salesman.

       

      When running the report, my charts correctly show the sales of 1 salesman but display the target of all salesmen located in the target file :

      Image 1843.jpg

       

       

      I've been trying to use inner joins, outer joins...in different places of my code with no luck. Looking at the code below, you might have an idea how to resolve my issue

      I believe it  would be possible to create a temporary table of salesmen located in the ZSQ42 table (load distinct ?)  and then link this temporary table and then load the Targets table only with the salesmen present in the temporary table...but I don't know how to proceed

       

      thanks for your help

      patrice

       

       

      [Hierarchie]:

      LOAD

      [Hierarchie] as [Hierarchie_Product],

      [Objective_Code] as [Objective_Product] ,

      [Language] as [Language_Product],

      [Code_Language] as [Code_Language_Product],

      [Range] as [Range_Product],

      [Family] as [Family_Product],

          [Family_Efficy] as [Family_Efficy_Product],

          [Range_Efficy] as [Range_Efficy_Product],

          [Not_Target] as [Not_Target_Product]

         

      FROM [lib://temp\Hierarchie.xlsx](ooxml, embedded labels, table is Hierarchie)

      WHERE [Language]='EN';

       

       

      Map_Hierarchie_to_Objetive_Code:

      Mapping load [Hierarchie_Product]as [hierarchie], [Objective_Product] resident Hierarchie;

       

       

      Map_Not_Target:

      Mapping load [Hierarchie_Product]as [hierarchie], [Not_Target_Product] resident Hierarchie;

       

       

       

       

      [countries]:

      LOAD

      [Code pays] ,

      [Continent],

      [Country],

          [Code_Efficy]

      FROM [lib://temp\Countries.xlsx] (ooxml, embedded labels, table is countries);

       

       

      Map_Countries:

      Mapping load [Code pays] as [zone_liv], [Code_Efficy] resident countries;

       

       

       

       

      [ZSQ042]:

      LOAD

      [code_client],

      [nom],

      [code_article],

      [designation],

      [hierarchie]  ,

          applymap('Map_Hierarchie_to_Objetive_Code',[hierarchie],null()) as [Target_Code],

          applymap('Map_Not_Target',[hierarchie],null()) as [Not_Target],

      [zone],

      [secteur] as [zone_vendeur],

      [qte_commandee],

      [date_saisie],

      [net_commande],

      [commande],

      [cde_achat],

      [cp],

      [ville],

      [region],

      [description_rg],

      [business_unit],

      [code_client_liv],

      [nom_liv],

      [zone_liv],

          applymap('Map_Countries',[zone_liv],null()) as [code_pays],

          year([date_saisie]) AS [Year],

      'Q'& ceil(month([date_saisie])/3) AS [Quarter],

      'Sem'& ceil(month([date_saisie])/6) AS [Semester],

      num(Month([date_saisie])) AS [Month],

      num(week([date_saisie])) AS [Week]

      FROM [lib://ZSQ\ZSQ042.xlsx]

      (ooxml, embedded labels, table is ZSQ042)

      where [hierarchie] <> 'PIECE' and [business_unit]='MX';

       

       

      drop table countries;

       

       

      [Targets]:

      LOAD

      [secteur] as [zone_vendeur],

      [secteur]&'_'&[code pays]&'_'&[target_code] as key,

      [2018 Target],

      [Code_Range_Efficy],

      [Q1],[Q2],[Q3],[Q4]

      FROM [lib://temp\Targets_Efficy.xlsx]

      (ooxml, embedded labels, table is targets);

       

       

       

       

       

       

      [Sales_Full_Year]:

      LOAD

      [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

      sum([qte_commandee]) as Qty_per_Year, [Year] as Year_Sales_Full_Year

      Resident ZSQ042

      where [Not_Target]<>'X'

      group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year];

       

       

       

       

      [Link]:

      //noconcatenate

       

       

      LOAD DISTINCT

      [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as Temp_Key,

          [zone_vendeur] as [Zone],[code_pays] as [Code pays],[Target_Code] as [Objective_Product]

          resident ZSQ042;

      //group by [zone_vendeur],[code_pays],[Target_Code],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code];

       

       

       

       

       

      concatenate ([Link])   

       

       

      LOAD DISTINCT

      [secteur]&'_'&[code pays]&'_'&[target_code] as key,

          [secteur] as [Zone],[code pays]  as [Code pays],[target_code] as [Objective_Product]

          FROM [lib://local\Targets_Efficy.xlsx](ooxml, embedded labels, table is targets);

      //where not exists (Temp_Key,[secteur] &'_'& [code pays] &'_'& [target_code]);

         

       

       

       

       

      drop field Temp_Key from Link;

      Drop Table ZSQ042;

        • Re: inner / outer join in Qlik desktop
          patrice salem

          Well, I don't know if my code is good looking but at least, I found a solution that works :

           

          //inner join to make sure all targets are counted when loading zsq042 for a single salesman

          [Targets]:

          load distinct

          [zone_vendeur]

          resident ZSQ042;

           

          inner join

          [Targets_temp]:

          LOAD

          [secteur] as [zone_vendeur],

          [secteur]&'_'&[code pays]&'_'&[target_code] as key,

          [2018 Target],

          [Code_Range_Efficy],

          [Q1],[Q2],[Q3],[Q4]

          FROM [lib://temp\Targets_Efficy.xlsx]

          (ooxml, embedded labels, table is targets);

           

           

           

           

           

           

          [Sales_Full_Year]:

          LOAD

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          sum([qte_commandee]) as Qty_per_Year, [Year] as Year_Sales_Full_Year

          Resident ZSQ042

          where [Not_Target]<>'X'

          group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year];

           

           

           

           

           

           

          [Sales_per_Semester]:

          LOAD

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          sum([qte_commandee]) as Qty_per_Semester,[Year] as Year_Sales_per_Semester ,[Semester]as Semester_Sales_per_Semester

          Resident ZSQ042

          where [Not_Target]<>'X'

          group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year],[Semester];

           

           

           

           

           

           

          [Sales_per_Quarter]:

          LOAD

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          sum([qte_commandee]) as Qty_per_Quarter,[Year] as Year_Sales_per_Quarter ,[Quarter]as Quarter_Sales_per_Quarter

          Resident ZSQ042

          where [Not_Target]<>'X'

          group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year],[Quarter];

           

           

           

           

           

           

          [Sales_per_Month]:

          LOAD

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          sum([qte_commandee]) as Qty_per_Month,[Year] as Year_Sales_per_Month ,[Month]as Month_Sales_per_Month

          Resident ZSQ042

          where [Not_Target]<>'X'

          group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year],[Month];

           

           

           

           

           

           

          [Sales_per_Week]:

          LOAD

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

          sum([qte_commandee]) as Qty_per_Week,[Year] as Year_Sales_per_Week ,[Week]as Week_Sales_per_Week

          Resident ZSQ042

          where [Not_Target]<>'X'

          group by [business_unit],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code],[Year],[Week];

           

           

           

           

           

           

          [Link]:

          //noconcatenate

           

           

          LOAD DISTINCT

          [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as key,

              [zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code] as Temp_Key,

              [zone_vendeur] as [Zone],[code_pays] as [Code pays],[Target_Code] as [Objective_Product]

              resident ZSQ042;

          //group by [zone_vendeur],[code_pays],[Target_Code],[zone_vendeur] &'_'& [code_pays] &'_'& [Target_Code];

           

           

           

           

           

          concatenate ([Link])   

           

           

          LOAD DISTINCT

          [secteur]&'_'&[code pays]&'_'&[target_code] as key,

              [secteur] as [Zone],[code pays]  as [Code pays],[target_code] as [Objective_Product]

              FROM [lib://local\Targets_Efficy.xlsx](ooxml, embedded labels, table is targets)

          where not exists (Temp_Key,[secteur] &'_'& [code pays] &'_'& [target_code]);

             

           

           

           

           

           

           

          //inner join to make sure countries appearing the country selection are those only having sales

          [countries]:

          load distinct

          [code_pays] as [Code pays]

          resident ZSQ042;

           

           

          inner join

           

           

          [countries_temp]:

          LOAD

          [Code pays] ,

          [Continent],

          [Country]

           

          FROM [lib://temp\Countries.xlsx]

          (ooxml, embedded labels, table is countries);

           

           

           

           

          //inner join to make sure salesmen appearing the salesmen selection are those only having sales

          [Vendeurs]:

          load distinct

          [zone_vendeur] as [Zone]

          resident ZSQ042;

           

           

          inner join

           

           

          [vendeurs_temp]:

          LOAD

          [Zone],

          [Name] AS [ASM],

          [zone] AS [Sales_Team]

          FROM [lib://temp\Areas.xlsx]

          (ooxml, embedded labels, table is Feuil1);

           

           

           

           

           

           

          drop field Temp_Key from Link;

          Drop Table ZSQ042;