3 Replies Latest reply: Jun 16, 2014 4:20 AM by Thomas Jensen RSS

    Mark a num on the repeating rows in script

    Gao Adam

      Hello Everybody,

      I'm coming across a problem and I need your help.

      I want to mark a num in the repeating rows to differ these rows into different rows.

      For example,I got a random table like this:

      COL1  COL2  COL3  COL4

        A         B        C        D

        A         B        C        D

        A         B        C        D

        B         C        D        E

        B         C        D        E

      THEN after handling I need to get a random table like this:

      COL1  COL2  COL3  COL4  COL5

        A         B        C        D        1

        A         B        C        D        2

        A         B        C        D        3

        B         C        D        E        1

        B         C        D        E        2

       

      so what methods can I use to solve this problem,thanks

        • Re: Mark a num on the repeating rows in script
          Manish Kachhia

          Temp:

          Load *, COL1 & COL2 & COL3 & COL4 as Key Inline

          [

          COL1,  COL2,  COL3,  COL4

            A,         B,        C,        D

            A,         B,        C,        D

            A,         B,        C,        D

            B,         C,       D,      E

            B,         C,        D,       E

            ];

           

          Final:

          Load

            *,

            IF(Key = Previous(Key), Peek('COL5')+1, 1) as COL5

          Resident Temp

          Order By COL1, COL2, COL3, COL4;

           

           

          Drop Table Temp;

          • Re: Mark a num on the repeating rows in script

            BaseTable:

            LOAD * INLINE [

            COL1, COL2, COL3, HowToGetThis?

            A, B, C, 1

            A, B, C, 1

            A, B, C, 1

            B, C, D, 1

            B, C, D, 1

             

             

            ];

             

            // Note: previous() reads from source table, uses original column names

            //       peek() reads from internal (destination) table, uses new names

             

             

            Example1:

            LOAD RowNo() As R1, COL1 AS COL1a, COL2 AS COL2a, COL3 AS COL3a,

                 if (previous(COL1) <> COL1, 1, peek('HowToGetThis?1') + 1) AS [HowToGetThis?1]

            RESIDENT BaseTable;