2 Replies Latest reply: Jun 25, 2016 11:17 AM by Андрей Шепель RSS

    Right join with saving some records

    Андрей Шепель

      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 codeNomenclature IdType of productAgent nameSalesPriority
      11Nom1Type1Andrey C542
      11Nom1Type1Boris D543
      11Nom1Type1Richard M541
      11Nom2Type1Andrey C332
      11Nom2Type1Boris D333
      11Nom2Type1Richard M331
      11Nom3Type1Andrey C42
      11Nom3Type1Boris D43
      11Nom3Type1Richard M41
      11Nom4Type2Andrey C763
      11Nom4Type2Boris D761
      11Nom4Type2Richard M762
      11Nom5Type3Andrey C761
      11Nom5Type3Boris D762
      11Nom5Type3Richard M763
      11Nom6Type3Andrey C541
      11Nom6Type3Boris D542
      11Nom6Type3Richard M543
      11Nom7Type3Andrey C321
      11Nom7Type3Boris D322
      11Nom7Type3Richard M323
      11Nom8Type3Andrey C671
      11Nom8Type3Boris D672
      11Nom8Type3Richard M673
      22Nom1Type1Anna F542
      22Nom1Type1Mike C541
      22Nom2Type1Anna F542
      22Nom2Type1Mike C541
      22Nom3Type1Anna F542
      22Nom3Type1Mike C541
      22Nom4Type2Anna F223
      22Nom4Type2Mike C222
      22Nom5Type3Anna F761
      22Nom5Type3Mike C763
      22Nom6Type3Anna F541
      22Nom6Type3Mike C543

       

      Then by right join function this table save only those agents, whicn have min priority in one Nomenclature Id by Outlet Code:

       

            

      Outlet codeNomenclature IdType of productAgent nameSalesPriority
      11Nom1Type1Richard M541
      11Nom2Type1Richard M331
      11Nom3Type1Richard M41
      11Nom4Type2Boris D761
      11Nom5Type3Andrey C761
      11Nom6Type3Andrey C541
      11Nom7Type3Andrey C321
      11Nom8Type3Andrey C671
      22Nom1Type1Mike C541
      22Nom2Type1Mike C541
      22Nom3Type1Mike C541
      22Nom4Type2Mike C222
      22Nom5Type3Anna F761
      22Nom6Type3Anna F541

       

      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 codeNomenclature IdType of productAgent nameSalesPriority
      11Nom1Type1Andrey C02
      11Nom1Type1Boris D03
      11Nom1Type1Richard M541
      11Nom2Type1Andrey C02
      11Nom2Type1Boris D03
      11Nom2Type1Richard M331
      11Nom3Type1Andrey C02
      11Nom3Type1Boris D03
      11Nom3Type1Richard M41
      11Nom4Type2Andrey C03
      11Nom4Type2Boris D01
      11Nom4Type2Richard M02
      11Nom5Type3Andrey C761
      11Nom5Type3Boris D02
      11Nom5Type3Richard M03
      11Nom6Type3Andrey C541
      11Nom6Type3Boris D02
      11Nom6Type3Richard M03
      11Nom7Type3Andrey C321
      11Nom7Type3Boris D02
      11Nom7Type3Richard M03
      11Nom8Type3Andrey C671
      11Nom8Type3Boris D02
      11Nom8Type3Richard M03
      22Nom1Type1Anna F02
      22Nom1Type1Mike C541
      22Nom2Type1Anna F02
      22Nom2Type1Mike C541
      22Nom3Type1Anna F02
      22Nom3Type1Mike C541
      22Nom4Type2Anna F03
      22Nom4Type2Mike C222
      22Nom5Type3Anna F761
      22Nom5Type3Mike C03
      22Nom6Type3Anna F541
      22Nom6Type3Mike C03

       

       

      There're model and source file in attachemnt.

      Hope for your help.

      Best regards.

        • Re: Right join with saving some records
          Sunny Talwar

          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;


          Capture.PNG