Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
patricesalem
Creator II
Creator II

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

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

Image 1821.jpg

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

Image 1820.jpg

Image 1819.jpg

[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) :

Image 1822.jpg

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

1 Solution

Accepted Solutions
patricesalem
Creator II
Creator II
Author

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

ScreenShot123.jpg

View solution in original post

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
patricesalem
Creator II
Creator II
Author

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.


talk is cheap, supply exceeds demand
patricesalem
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

ScreenShot123.jpg