20 Replies Latest reply: Jul 3, 2015 3:45 AM by Андрей Шепель RSS

    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.