Mark a num on the repeating rows in script

[adamjiawen]

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

[MK_QSL · Accepted solution]

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;

[adamjiawen]

Thank you very much for your help Manish,The function Peek is really a useful function.It's a good lesson to me.3Q~

[#4]

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;