Left outer join when loading tables / Qlik desktop - script issue

[patricesalem]

Hello

I have 5 tables that help me building up the follow-up of targets.

[Vendeurs] - contains salesmen zone codes (=[Zone], names and sales team

[Targets] - contains all targets by salesman & country & product group

[ZSQ042_Target] - contains all sales by salesman & country & product group by year

[Hierarchie] - contains the product group code ([Hierarchie], Family description, Range description

[Countries] -- contains country code, country description and continent

I would like to create a report where all data included in the Targets table appear along with the sales of the year (even if no sales are made)

After several trials, I can only see target figures in countries where something has been sold. So, if no sales are made, I don't see the target to reach.

At the moment (the vcf file is attached) :

[countries]:

LOAD

[Code pays] AS [Code pays-zone],

[Continent],

[Country],

APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Country])), '-') AS [countries.Country_GeoInfo]

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

(ooxml, embedded labels, table is countries);

[Vendeurs]:

LOAD

[Zone] AS [Marché_régional-secteur],

[Name] AS [ASM],

[zone] AS [Sales_Team]

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

(ooxml, embedded labels, table is Feuil1);

[Targets]:

LOAD

[secteur] as [Marché_régional-secteur]  ,

[code pays] as [Code pays-zone],

[Objective_Code] as [Targets.Objective_Code],

[2018 Target],

[Code_Range_Efficy],

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

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

(ooxml, embedded labels, table is targets);

[Hierarchie]:

LOAD

[Hierarchie] AS [Hierarchie-hierarchie],

[Objective_Code] AS [Targets.Objective_Code],

[Language],

[Code_Language],

[Range],

[Family]

FROM [lib://temp/Hierarchie.xlsx]

(ooxml, embedded labels, table is Hierarchie)

WHERE [Language]='EN';

[ZSQ042_Target]:

LOAD

[Code pays-zone] as [Code pays-zone],

[hierarchie] ,

[Marché_régional-secteur] as [Marché_régional-secteur],

year(Today()) as [Period],

sum([qte_commandee]) as Qty

Resident ZSQ042

where year([date_saisie]) = year(Today())

group by [Code pays-zone], [hierarchie], [Marché_régional-secteur],year([date_saisie]);

Drop Table ZSQ042;

Looking at the following structure, I'm missing a join from [ZSQ042_Target.hierarchie] to [Hierarchie.Hierarchie-hierarchie].

Each time I try to create it (replacing [Hierarchie] by [Hierarchie-hierarchie] in [ZSQ042_Target], I end up with a loop issue

[Targets].[Targets.Ojective.Code] should be linked to [Hierarchie].[Targets.Objective.Code]  => done

[Hierarchie].[Hierarchie-hierarchie] should be linked to [ZSQ042_Target].hierarchie => not done

[Targets].[secteur] sould be linked to [ZSQ042_Target].[Marché_régional-secteur]  and [Code-Pays-zone] (targets are set by salesmane zone number and country) :

So to summarize, I need to create a link between the table [Hiearchie] and ZSQ042_Target, based on the fields Hierarchie / Hierarchie.Hierarchie.

I need to display all data included in Targets, having made sales (ZSQ042_Target) or not for the related salesman zone / country / hierarchie

Thanks A LOT in advance for your help...I've been stuck on this issue for hours

Patrice

[Gysbert_Wassenaar]

Try concatenating the targets and the sales into one table. Read this blog post for more information: Fact Table with Mixed Granularity

[patricesalem]

Hello

It looks like it is what I'm looking for...nevertheless, it seems to be overcomplicated for me.

I understand that I need to map tables...and aggregate them.

Any start of futher help would be more than welcome

pat

[Gysbert_Wassenaar]

Have you read the technical brief as well? Generic keys‌

Example 2 in the pdf starting on page 15 looks what you need.

Mapping tables can be used, but are not needed. I don't see that you need to aggregate them either.

[patricesalem]

Gysbert

I'm going through the file (page 15 to 18) and indeed the structure is not the same than mine.

If I don't need to map the tables and don't need to aggregate them, then which part of the pdf is a reference to me ?

It looks like it is only about mapping tables and then applying the map to the sales table.

Sorry, I'm lost

[patricesalem · Accepted solution]

Hello

I did find the solution using mapping and link :

] 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]

FROM [lib://local\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;

[countries]:

LOAD

[Code pays] ,

[Continent],

[Country],

    [Code_Efficy]

FROM [lib://local\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],

[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]), '00') AS [Month],

num(week([date_saisie]),'00') AS [Week]

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

(ooxml, embedded labels, table is ZSQ042)

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

drop table countries;

[Targets]:

LOAD

//[secteur],

    //[code pays],

//[target_code],

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

[2018 Target],

[Code_Range_Efficy],

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

FROM [lib://local\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 year([date_saisie]) = year(Today())

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 year([date_saisie]) = year(Today())

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 year([date_saisie]) = year(Today())

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

[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 year([date_saisie]) = year(Today())

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

drop field Temp_Key from Link;

Drop Table ZSQ042;

[countries]:

LOAD

[Code pays] ,

[Continent],

[Country]

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

(ooxml, embedded labels, table is countries);

[Vendeurs]:

LOAD

[Zone],

[Name] AS [ASM],

[zone] AS [Sales_Team]

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

(ooxml, embedded labels, table is Feuil1);