2 Replies Latest reply: Jul 26, 2018 10:24 AM by Mahitha M RSS

    Logic help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.

      I have three levels like H,L,M in Level column,

      first priority is H, Second Priority is L,Third priority is M.

       

      1.If any employee is in Three Levels for example in the below sample data KELLY is in 3 levels but need to put him in only Level=H

      and remaining two rows need to remove in the table.

      2. JOHN is in L,M two levels L is the high priority compared to M so need to keep only L row and remove M row of JOHN

      3.GOW is in only one level i.e., M so need to keep as it is.

       

      The above fields contains the sample data like below. All these fields are in same table in the backend.

       

      Original data:

      Name        Level         Sales      DESIGNATION          Created              Status

      KELLY      H               5263       MANAGER                25/07/2018

      KELLY      L                5123       MANAGER                                            Active

      KELLY      M               8690       ASS MANAGER        30/05/2018

      JOHN       L                 5699       MANAGER

      JOHN       M                583        ASS MANAGER                                    Active

      GOW        M                5869       MANAGER 



      Expected data Output:

      Name        Level         Sales      DESIGNATION          Created              Status

      KELLY      H               5263       MANAGER                25/07/2018

      JOHN       L                 5699       MANAGER

      GOW        M                5869       MANAGER 

       

      Please help me on this.

       

      Thanks in advance

        • Re: Logic help
          Sunny Talwar

          Try this

           

          Table:

          LOAD *,

          Match(Level, 'M', 'L', 'H') as LevelNum;

          LOAD * INLINE [

              Name, Level, Sales, DESIGNATION, Created, Status

              KELLY, H, 5263, MANAGER, 25/07/2018

              KELLY, L, 5123, MANAGER, , Active

              KELLY, M, 8690, ASS MANAGER, 30/05/2018

              JOHN, L, 5699, MANAGER

              JOHN, M, 583, ASS MANAGER, , Active

              GOW, M, 5869, MANAGER

          ];


          Right Join (Table)

          LOAD Name,

          Max(LevelNum) as LevelNum

          Resident Table

          Group By Name;