Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andreyfcdk91
New Contributor III

Right join with saving some records

Good day!

Colleagues, in some of my last posts i described problem with assigments sales by Nomenclatue product type priorities.

In result i have table, which have sales by agents, outlet codes, nomenclatures and product type:

      

Outlet codeNomenclature IdType of productAgent nameSalesPriority
11Nom1Type1Andrey C542
11Nom1Type1Boris D543
11Nom1Type1Richard M541
11Nom2Type1Andrey C332
11Nom2Type1Boris D333
11Nom2Type1Richard M331
11Nom3Type1Andrey C42
11Nom3Type1Boris D43
11Nom3Type1Richard M41
11Nom4Type2Andrey C763
11Nom4Type2Boris D761
11Nom4Type2Richard M762
11Nom5Type3Andrey C761
11Nom5Type3Boris D762
11Nom5Type3Richard M763
11Nom6Type3Andrey C541
11Nom6Type3Boris D542
11Nom6Type3Richard M543
11Nom7Type3Andrey C321
11Nom7Type3Boris D322
11Nom7Type3Richard M323
11Nom8Type3Andrey C671
11Nom8Type3Boris D672
11Nom8Type3Richard M673
22Nom1Type1Anna F542
22Nom1Type1Mike C541
22Nom2Type1Anna F542
22Nom2Type1Mike C541
22Nom3Type1Anna F542
22Nom3Type1Mike C541
22Nom4Type2Anna F223
22Nom4Type2Mike C222
22Nom5Type3Anna F761
22Nom5Type3Mike C763
22Nom6Type3Anna F541
22Nom6Type3Mike C543

Then by right join function this table save only those agents, whicn have min priority in one Nomenclature Id by Outlet Code:

      

Outlet codeNomenclature IdType of productAgent nameSalesPriority
11Nom1Type1Richard M541
11Nom2Type1Richard M331
11Nom3Type1Richard M41
11Nom4Type2Boris D761
11Nom5Type3Andrey C761
11Nom6Type3Andrey C541
11Nom7Type3Andrey C321
11Nom8Type3Andrey C671
22Nom1Type1Mike C541
22Nom2Type1Mike C541
22Nom3Type1Mike C541
22Nom4Type2Mike C222
22Nom5Type3Anna F761
22Nom6Type3Anna F541

I need to save this result, but to save those agents (Set them Sales = 0), which have larger priority of [Type of product type] in Outlet Code by each Nomeclature Id.

Necessary result:

Outlet codeNomenclature IdType of productAgent nameSalesPriority
11Nom1Type1Andrey C02
11Nom1Type1Boris D03
11Nom1Type1Richard M541
11Nom2Type1Andrey C02
11Nom2Type1Boris D03
11Nom2Type1Richard M331
11Nom3Type1Andrey C02
11Nom3Type1Boris D03
11Nom3Type1Richard M41
11Nom4Type2Andrey C03
11Nom4Type2Boris D01
11Nom4Type2Richard M02
11Nom5Type3Andrey C761
11Nom5Type3Boris D02
11Nom5Type3Richard M03
11Nom6Type3Andrey C541
11Nom6Type3Boris D02
11Nom6Type3Richard M03
11Nom7Type3Andrey C321
11Nom7Type3Boris D02
11Nom7Type3Richard M03
11Nom8Type3Andrey C671
11Nom8Type3Boris D02
11Nom8Type3Richard M03
22Nom1Type1Anna F02
22Nom1Type1Mike C541
22Nom2Type1Anna F02
22Nom2Type1Mike C541
22Nom3Type1Anna F02
22Nom3Type1Mike C541
22Nom4Type2Anna F03
22Nom4Type2Mike C222
22Nom5Type3Anna F761
22Nom5Type3Mike C03
22Nom6Type3Anna F541
22Nom6Type3Mike C03

There're model and source file in attachemnt.

Hope for your help.

Best regards.

1 Solution

Accepted Solutions

Re: Right join with saving some records

May be this:

mapProduct:

mapping LOAD [Nomenclature Id],

    [Type of product]

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is [Nomenclature classification]);

Sales:

LOAD [Outlet code],

    [Nomenclature Id],

    ApplyMap('mapProduct',[Nomenclature Id]) as [Type of product],

    Sales

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is Sales);

Join

LOAD [Outlet code],

    [Agent name],

    [Type of team]

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is Agents);

Join

LOAD [Type of product],

    [Type of team],

    Priority

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is [Assigment sales]);

Left join

LOAD [Outlet code],

  [Type of product],

  Min(Priority) as Priority,

  1 as Flag

Resident Sales

Group By [Outlet code], [Type of product];

FinalSales:

NoConcatenate

LOAD [Outlet code],

  [Nomenclature Id],

  [Type of product],

  If(Flag = 1, Sales, 0) as Sales,

  [Agent name],

  [Type of team],

  Priority

Resident Sales;

DROP Table Sales;


Capture.PNG

2 Replies

Re: Right join with saving some records

May be this:

mapProduct:

mapping LOAD [Nomenclature Id],

    [Type of product]

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is [Nomenclature classification]);

Sales:

LOAD [Outlet code],

    [Nomenclature Id],

    ApplyMap('mapProduct',[Nomenclature Id]) as [Type of product],

    Sales

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is Sales);

Join

LOAD [Outlet code],

    [Agent name],

    [Type of team]

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is Agents);

Join

LOAD [Type of product],

    [Type of team],

    Priority

FROM

[Sales by teams.xlsx]

(ooxml, embedded labels, table is [Assigment sales]);

Left join

LOAD [Outlet code],

  [Type of product],

  Min(Priority) as Priority,

  1 as Flag

Resident Sales

Group By [Outlet code], [Type of product];

FinalSales:

NoConcatenate

LOAD [Outlet code],

  [Nomenclature Id],

  [Type of product],

  If(Flag = 1, Sales, 0) as Sales,

  [Agent name],

  [Type of team],

  Priority

Resident Sales;

DROP Table Sales;


Capture.PNG

andreyfcdk91
New Contributor III

Re: Right join with saving some records

Thanks, Sunny

Community Browser