Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
Thanks, Sunny