inner / outer join in Qlik desktop

[patricesalem]

Hello

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

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 :

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;

[patricesalem · Accepted solution]

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;