Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Assigment sales by agents with product type condition

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 codeNomenclature IdType of productAgent nameSales
11Nom1Type1Richard M54Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority
11Nom2Type1Richard M33Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority
11Nom3Type1Richard M4Because for Type of product 'Type1' Type of team 'Team1' have the smallest Priority
11Nom4Type2Boris D76Because for Type of product 'Type2' Type of team 'Team2' have the smallest Priority
11Nom5Type3Andrey C76Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority
11Nom6Type3Andrey C54Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority
11Nom7Type3Andrey C32Because for Type of product 'Type3' Type of team 'Team3' have the smallest Priority
11Nom8Type3Andrey C67Because 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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

20 Replies
sunny_talwar

How do we know which Team has Smallest Priority? Which field shows that information?

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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 productType of teamPriority
Type1Team11
Type1Team23
Type1Team32

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)

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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)

  1. 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)

  1. 3.Then, Nom5,Nom6,Nom7,Nom8 has Type of product 'Type3'.

  

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

Anonymous
Not applicable
Author

Hi,

try if this can help you.

Regards.

Anonymous
Not applicable
Author

Thanks.

But in your solution (for Outlet  code 11) saels are dublicated by every agent

     

Outlet codeNomenclature IdType of productAgent nameSales
11Nom1Type1Andrey C54
11Nom1Type1Boris D54
11Nom1Type1Richard M54
11Nom2Type1Andrey C33
11Nom2Type1Boris D33
11Nom2Type1Richard M33
11Nom3Type1Andrey C4
11Nom3Type1Boris D4
11Nom3Type1Richard M4
11Nom4Type2Andrey C76
11Nom4Type2Boris D76
11Nom4Type2Richard M76
11Nom5Type3Andrey C76
11Nom5Type3Boris D76
11Nom5Type3Richard M76
11Nom6Type3Andrey C54
11Nom6Type3Boris D54
11Nom6Type3Richard M54
11Nom7Type3Andrey C32
11Nom7Type3Boris D32
11Nom7Type3Richard M32
11Nom8Type3Andrey C67
11Nom8Type3Boris D67
11Nom8Type3Richard M67

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 productType of teamPriority
Type2Team12
Type2Team21
Type2Team33


For
Nom4
must be agent with Type of team Team2 (Boris D)

  1. 3.Then, Nom5,Nom6,Nom7,Nom8 has Type of product 'Type3'.

 

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

Thanks.

Best regards.