Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
adamjiawen
New Contributor II

Mark a num on the repeating rows in script

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Mark a num on the repeating rows in script

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;

3 Replies
MVP
MVP

Re: Mark a num on the repeating rows in script

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
New Contributor II

Re: Mark a num on the repeating rows in script

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

Not applicable

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;

Community Browser