Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day!
In my model i load main table Sales in load script from excel:
Sales:
LOAD
[Outlet code],
[Nomenclature Id],
Sales
FROM
[Sales by teams].xlsx
(ooxml, embedded labels, table is Sales);
then i join ti this table nomenclature classificator from the same file:
left keep(Sales)
load
[Nomenclature Id],
[Type of product]
FROM
[Sales by teams].xlsx
(ooxml, embedded labels, table is [Nomenclature classification]);
In this file (excel) i also have agent classificator, which is situated in sheet Agents and i join it to table Sales:
left keep(Sales)
load
[Outlet code],
[Agent name],
[Type of team]
FROM
[Sales by teams].xlsx
(ooxml, embedded labels, table is Agents);
Each of outlet codes is connected with two or three agents.
I need connect Agent to sales by priority table which is situated in sheet Assigment sales.
For example:
First Outlet code 22 is connected with three Agents:
Andrey C (Team3)
Boris D (Team2)
Richard M (Team1)
Each of Nomenclature Id must conneted with Agent by min priority of it's Type of product.
Result table for Outlet code 22 must be:
Outlet code | Nomenclature Id | Type of product | Agent name | Sales | |
11 | Nom1 | Type1 | Richard M | 54 | Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority |
11 | Nom2 | Type1 | Richard M | 33 | Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority |
11 | Nom3 | Type1 | Richard M | 4 | Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority |
11 | Nom4 | Type2 | Boris D | 76 | Because for Type of product 'Type2' Type of team 'Team2' have the smallest Priority |
11 | Nom5 | Type3 | Andrey C | 76 | Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority |
11 | Nom6 | Type3 | Andrey C | 54 | Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority |
11 | Nom7 | Type3 | Andrey C | 32 | Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority |
11 | Nom8 | Type3 | Andrey C | 67 | Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority |
In attachment model and source file.
Please, help!!!
It's very important for me.
Thanks.
Perhaps I forgot to save the document before posting it. Let's try again. See attached qvw
How do we know which Team has Smallest Priority? Which field shows that information?
Hi,
excuse me but it is not clear.
Which is the filed with priority?
Why in yhe result table for a outlet code 22 you have outelet code 11?
Maybe I dont understand.
Regards.
There's table In sheet Assigment sales. (In excel file)
For example:
Nomenclature Id's with Type of product 'Type1' has following prioirities among type of teams:
Type of product | Type of team | Priority |
Type1 | Team1 | 1 |
Type1 | Team2 | 3 |
Type1 | Team3 | 2 |
That's why, if Outlet code connected with, for example agents with Type of team 'Team2' and 'Team3', sales must connected to agent with Type of team 'Team3' (because 2<3)
Hi.
Sorry, there must be Outlet code 11
in sheet Assigment sales you can find priority tables among types of teams and Type of products
For example:
Outlet code 11 has sales with all Nomenclature Id (Nom1-Nom8). Nom1, Nom2, Nom3 has Type of product 'Type1'.
Outlet code 11 is connected with three agents, that's why, according to priority table for Type of product 'Type1':
Type of product | Type of team | Priority |
Type1 | Team1 | 1 |
Type1 | Team2 | 3 |
Type1 | Team3 | 2 |
for Nom1 Nom2 and Nom3 must be agent with Type of team Team1 (Richard M)
Type of product | Type of team | Priority |
Type2 | Team1 | 2 |
Type2 | Team2 | 1 |
Type2 | Team3 | 3 |
For Nom4 must be agent with Type of team Team2 (Boris D)
Type of product | Type of team | Priority |
Type3 | Team1 | 3 |
Type3 | Team2 | 2 |
Type3 | Team3 | 1 |
For Nom5,Nom6,Nom7,Nom8 must be agent with Type of team Team3 (Andrey C)
Result table for Outlet code 11 must be:
Outlet code | Nomenclature Id | Type of product | Agent name | Sales |
11 | Nom1 | Type1 | Richard M | 54 |
11 | Nom2 | Type1 | Richard M | 33 |
11 | Nom3 | Type1 | Richard M | 4 |
11 | Nom4 | Type2 | Boris D | 76 |
11 | Nom5 | Type3 | Andrey C | 76 |
11 | Nom6 | Type3 | Andrey C | 54 |
11 | Nom7 | Type3 | Andrey C | 32 |
11 | Nom8 | Type3 | Andrey C | 67 |
And the same rules for second outlet
Hi,
try if this can help you.
Regards.
Thanks.
But in your solution (for Outlet code 11) saels are dublicated by every agent
Outlet code | Nomenclature Id | Type of product | Agent name | Sales |
11 | Nom1 | Type1 | Andrey C | 54 |
11 | Nom1 | Type1 | Boris D | 54 |
11 | Nom1 | Type1 | Richard M | 54 |
11 | Nom2 | Type1 | Andrey C | 33 |
11 | Nom2 | Type1 | Boris D | 33 |
11 | Nom2 | Type1 | Richard M | 33 |
11 | Nom3 | Type1 | Andrey C | 4 |
11 | Nom3 | Type1 | Boris D | 4 |
11 | Nom3 | Type1 | Richard M | 4 |
11 | Nom4 | Type2 | Andrey C | 76 |
11 | Nom4 | Type2 | Boris D | 76 |
11 | Nom4 | Type2 | Richard M | 76 |
11 | Nom5 | Type3 | Andrey C | 76 |
11 | Nom5 | Type3 | Boris D | 76 |
11 | Nom5 | Type3 | Richard M | 76 |
11 | Nom6 | Type3 | Andrey C | 54 |
11 | Nom6 | Type3 | Boris D | 54 |
11 | Nom6 | Type3 | Richard M | 54 |
11 | Nom7 | Type3 | Andrey C | 32 |
11 | Nom7 | Type3 | Boris D | 32 |
11 | Nom7 | Type3 | Richard M | 32 |
11 | Nom8 | Type3 | Andrey C | 67 |
11 | Nom8 | Type3 | Boris D | 67 |
11 | Nom8 | Type3 | Richard M | 67 |
What i need:
Outlet code 11 has sales with all Nomenclature Id (Nom1-Nom8).
Nom1, Nom2, Nom3 has Type of product 'Type1'.
Outlet code 11 is connected with three agents, that's why, according to priority table for Type of product 'Type1':
Type of product | Type of team | Priority |
Type1 | Team1 | 1 |
Type1 | Team2 | 3 |
Type1 | Team3 | 2 |
for Nom1 Nom2 and Nom3 must be agent with Type of team Team1 (Richard M)
2.Then, Nom4 has Type of product 'Type2'.
Type of product | Type of team | Priority |
Type2 | Team1 | 2 |
Type2 | Team2 | 1 |
Type2 | Team3 | 3 |
For Nom4 must be agent with Type of team Team2 (Boris D)
Type of product | Type of team | Priority |
Type3 | Team1 | 3 |
Type3 | Team2 | 2 |
Type3 | Team3 | 1 |
For Nom5,Nom6,Nom7,Nom8 must be agent with Type of team Team3 (Andrey C)
Result table for Outlet code 11 must be:
Outlet code | Nomenclature Id | Type of product | Agent name | Sales |
11 | Nom1 | Type1 | Richard M | 54 |
11 | Nom2 | Type1 | Richard M | 33 |
11 | Nom3 | Type1 | Richard M | 4 |
11 | Nom4 | Type2 | Boris D | 76 |
11 | Nom5 | Type3 | Andrey C | 76 |
11 | Nom6 | Type3 | Andrey C | 54 |
11 | Nom7 | Type3 | Andrey C | 32 |
11 | Nom8 | Type3 | Andrey C | 67 |
And the same rules for second outlet
Hi,
sorry but I dont understand if the priority is from type of product or type of team or together.
I understand that you want a table with data without duplicate.
I hope another person kan help you.
Have a nice evening.
Regards.
Thanks.
Best regards.