6 Replies Latest reply: Oct 15, 2013 12:13 PM by Clever Anjos RSS

    Mark/flag identical field values or just keep the first occurring value

    Peter Schulz

      Hi,

       

      I have the following table(INPUT) and I want it to look like OUTPUT1 or OUTPUT2 at the end.

       

      INPUT:

      LOAD * INLINE [

          Key1, Key2, value1

          100, 1, a

          100, 2, b

          100, 3, a

          100, 4, c

          100, 5, a

          100, 6, d

          101, 1, a

          101, 2, b

          101, 3, c

          101, 4, d

          101, 5, c

          101, 6, d

          101, 7, c

          101, 8, a

      ];

      OUTPUT1:

      LOAD * INLINE [

          Key1, Key2, value1, flag

          100, 1, a, 1

          100, 2, b, 1

          100, 3, a, 2

          100, 4, c, 1

          100, 5, a, 3

          100, 6, d, 1

          101, 1, a, 1

          101, 2, b, 1

          101, 3, c, 1

          101, 4, d, 1

          101, 5, c, 2

          101, 6, d, 2

          101, 7, c, 3

          101, 8, a, 2

      ];

      OUTPUT2:

      LOAD * INLINE [

          Key1, Key2, value1

          100, 1, a

          100, 2, b

          100, 3,

          100, 4, c

          100, 5,

          100, 6, d

          101, 1, a

          101, 2, b

          101, 3, c

          101, 4, d

          101, 5,

          101, 6,

          101, 7,

          101, 8,

      ];

       

      I would prefer Output1 but at the moment it is not important. I just have to make sure that I only take the first occurring value if there are 2 identical values with the same key1.

       

      Background:

      I need to do this, because I create generic tables afterwards which I join into another table. When there are 2 identical values I finally get much more rows to join than I want and need.   With a flag or null() I could simply use a Where ...

       

      I appreciate any help on this...

       

      regards, Peter

       

      Message was edited by: Forgot the attachment, with the tables, if someone needs it.

        • Re: Mark/flag identical field values or just keep the first occurring value
          Stefan Wühl

          If you don't have to many combinations of Key1 and value1, you can use autonumber for option 1

           

          INPUT:

          LOAD *,AutoNumber(recno(), Key1&value1 ) as Flag  INLINE [

              Key1, Key2, value1

              100, 1, a

              100, 2, b

              100, 3, a

              100, 4, c

              100, 5, a

              100, 6, d

              101, 1, a

              101, 2, b

              101, 3, c

              101, 4, d

              101, 5, c

              101, 6, d

              101, 7, c

              101, 8, a

          ];

            • Re: Mark/flag identical field values or just keep the first occurring value
              Stefan Wühl

              And for your option 2 maybe:

               

              INPUT:

              LOAD *, Key1&value1 as TempKey INLINE [

                  Key1, Key2, value1

                  100, 1, a

                  100, 2, b

                  100, 3, a

                  100, 4, c

                  100, 5, a

                  100, 6, d

                  101, 1, a

                  101, 2, b

                  101, 3, c

                  101, 4, d

                  101, 5, c

                  101, 6, d

                  101, 7, c

                  101, 8, a

              ];

               

              RESULT:

              Left Join (INPUT) LOAD

                   Key1,

                   Key2,

                   TempKey as TempKey2,

                   value1 as flagvalue

              Resident INPUT

              where not exists(TempKey2,TempKey);

               

              drop fields TempKey, TempKey2;

            • Re: Mark/flag identical field values or just keep the first occurring value
              Clever Anjos

              If you want only flag the first ocorrence you can try this

               

              INPUT:

              LOAD *, Exists(Key2) as Flag INLINE [

                  Key1, Key2, value1

                  100, 1, a

                  100, 2, b

                  100, 3, a

                  100, 4, c

                  100, 5, a

                  100, 6, d

                  101, 1, a

                  101, 2, b

                  101, 3, c

                  101, 4, d

                  101, 5, c

                  101, 6, d

                  101, 7, c

                  101, 8, a

              ];

               

              Flag = 0 // First occorence

              Flag = 1 // 2nd, 3nd...

              • Re: Mark/flag identical field values or just keep the first occurring value
                Manas BN

                Adding to the many good suggestions.

                 

                This is for Output 1

                 

                LOAD *
                ,
                RecNo() as ID                                     // Create ID Field
                INLINE [
                    Key1, Key2, value1
                    100, 1, a
                    100, 2, b
                    100, 3, a
                    100, 4, c
                    100, 5, a
                    100, 6, d
                    101, 1, a
                    101, 2, b
                    101, 3, c
                    101, 4, d
                    101, 5, c
                    101, 6, d
                    101, 7, c
                    101, 8, a
                ]
                ;

                // Join Rank to Fact table
                LEFT JOIN (Fact)
                LOAD
                ID
                ,
                if(value1=Previous(value1) and Key1=Previous(Key1),peek(Rank)+1,1) as Rank
                RESIDENT Fact
                ORDER BY value1,Key1,Key2  ;

                Cheers!

                 


                • Re: Mark/flag identical field values or just keep the first occurring value
                  Peter Schulz

                  Hi again,

                  sorry that I needed so long to answer.

                   

                  At first, thank you all so much for your contributions and the great work. I was on the totally wrong direction with my tries.

                   

                  Since I was not sure whom to give the points I decided to try all the solutions. So I created a QVW and here you can see the results:

                   

                  solutions_qv.jpg

                   

                  I attached the corresponding QVW file, so you can check it out by yourself. The solution No. 2 from Swuehl is exactly what I need. So I can integrate the whole thing in an earlier step in my script.

                   

                  So swuehl got the "Correct Answer" and the other 2 a "Helpful Answer". In the script from Clever Anjos is something going wrong, looks like one of my earlier tries.

                   

                  Thank you all very much and have a nice day.

                   

                  - Peter -