Right join with saving some records

[Anonymous]

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 code	Nomenclature Id	Type of product	Agent name	Sales	Priority
11	Nom1	Type1	Andrey C	54	2
11	Nom1	Type1	Boris D	54	3
11	Nom1	Type1	Richard M	54	1
11	Nom2	Type1	Andrey C	33	2
11	Nom2	Type1	Boris D	33	3
11	Nom2	Type1	Richard M	33	1
11	Nom3	Type1	Andrey C	4	2
11	Nom3	Type1	Boris D	4	3
11	Nom3	Type1	Richard M	4	1
11	Nom4	Type2	Andrey C	76	3
11	Nom4	Type2	Boris D	76	1
11	Nom4	Type2	Richard M	76	2
11	Nom5	Type3	Andrey C	76	1
11	Nom5	Type3	Boris D	76	2
11	Nom5	Type3	Richard M	76	3
11	Nom6	Type3	Andrey C	54	1
11	Nom6	Type3	Boris D	54	2
11	Nom6	Type3	Richard M	54	3
11	Nom7	Type3	Andrey C	32	1
11	Nom7	Type3	Boris D	32	2
11	Nom7	Type3	Richard M	32	3
11	Nom8	Type3	Andrey C	67	1
11	Nom8	Type3	Boris D	67	2
11	Nom8	Type3	Richard M	67	3
22	Nom1	Type1	Anna F	54	2
22	Nom1	Type1	Mike C	54	1
22	Nom2	Type1	Anna F	54	2
22	Nom2	Type1	Mike C	54	1
22	Nom3	Type1	Anna F	54	2
22	Nom3	Type1	Mike C	54	1
22	Nom4	Type2	Anna F	22	3
22	Nom4	Type2	Mike C	22	2
22	Nom5	Type3	Anna F	76	1
22	Nom5	Type3	Mike C	76	3
22	Nom6	Type3	Anna F	54	1
22	Nom6	Type3	Mike C	54	3

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

Outlet code	Nomenclature Id	Type of product	Agent name	Sales	Priority
11	Nom1	Type1	Richard M	54	1
11	Nom2	Type1	Richard M	33	1
11	Nom3	Type1	Richard M	4	1
11	Nom4	Type2	Boris D	76	1
11	Nom5	Type3	Andrey C	76	1
11	Nom6	Type3	Andrey C	54	1
11	Nom7	Type3	Andrey C	32	1
11	Nom8	Type3	Andrey C	67	1
22	Nom1	Type1	Mike C	54	1
22	Nom2	Type1	Mike C	54	1
22	Nom3	Type1	Mike C	54	1
22	Nom4	Type2	Mike C	22	2
22	Nom5	Type3	Anna F	76	1
22	Nom6	Type3	Anna F	54	1

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 code	Nomenclature Id	Type of product	Agent name	Sales	Priority
11	Nom1	Type1	Andrey C	0	2
11	Nom1	Type1	Boris D	0	3
11	Nom1	Type1	Richard M	54	1
11	Nom2	Type1	Andrey C	0	2
11	Nom2	Type1	Boris D	0	3
11	Nom2	Type1	Richard M	33	1
11	Nom3	Type1	Andrey C	0	2
11	Nom3	Type1	Boris D	0	3
11	Nom3	Type1	Richard M	4	1
11	Nom4	Type2	Andrey C	0	3
11	Nom4	Type2	Boris D	0	1
11	Nom4	Type2	Richard M	0	2
11	Nom5	Type3	Andrey C	76	1
11	Nom5	Type3	Boris D	0	2
11	Nom5	Type3	Richard M	0	3
11	Nom6	Type3	Andrey C	54	1
11	Nom6	Type3	Boris D	0	2
11	Nom6	Type3	Richard M	0	3
11	Nom7	Type3	Andrey C	32	1
11	Nom7	Type3	Boris D	0	2
11	Nom7	Type3	Richard M	0	3
11	Nom8	Type3	Andrey C	67	1
11	Nom8	Type3	Boris D	0	2
11	Nom8	Type3	Richard M	0	3
22	Nom1	Type1	Anna F	0	2
22	Nom1	Type1	Mike C	54	1
22	Nom2	Type1	Anna F	0	2
22	Nom2	Type1	Mike C	54	1
22	Nom3	Type1	Anna F	0	2
22	Nom3	Type1	Mike C	54	1
22	Nom4	Type2	Anna F	0	3
22	Nom4	Type2	Mike C	22	2
22	Nom5	Type3	Anna F	76	1
22	Nom5	Type3	Mike C	0	3
22	Nom6	Type3	Anna F	54	1
22	Nom6	Type3	Mike C	0	3

There're model and source file in attachemnt.

Hope for your help.

Best regards.

[sunny_talwar · Accepted solution]

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;

[Anonymous]

Thanks, Sunny