Assigment sales by agents with product type condition

[Anonymous]

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.

[Anonymous]

Could you post your model again?

[Anonymous]

Excuse me, my model! 🙂

(I delete the post, sorry!).

Best regards.

[Gysbert_Wassenaar]

See attached qvw.

[Anonymous]

Hi,

if I can you shhould resolve Synt key before...

Best regards.

[Anonymous]

Many thanks for your help.

But why in result table for Outlet code 22 and Nom4 we have missing agent?

Outlet code 22 is connected with two agents:

Mike C	Team1
Anna F	Team3

Nom4 have Type of product 'Type2';

For Type2 in Priority table we have:

Type2	Team1	2
Type2	Team2	1
Type2	Team3	3

That's why in result row for Outlet code 22 for Nom4 must be agent Mike C (because 2<3)

Thank you

[Gysbert_Wassenaar]

And 1 is smaller than 2. The priority for Type2 is 1. That's Team2. And there's not Agent for Team2 for Outlet code 22.

[Anonymous]

Ok. But there no agent with Team2 for Outlet code 22.

Min priority must be among agents which connected with outlets.(in this case among 2 and 3 priority)

Is it possible to implement?

[Gysbert_Wassenaar]

See attached qvw

[Anonymous]

Sorry, but in this model result is the same..

[Gysbert_Wassenaar · Accepted solution]

Perhaps I forgot to save the document before posting it. Let's try again. See attached qvw