topic Mark a num on the repeating rows in script in QlikView

Mark a num on the repeating rows in script

adamjiawen — Mon, 16 Jun 2014 07:39:49 GMT

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

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

MK_QSL — Mon, 16 Jun 2014 07:57:54 GMT

Temp:

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

[

COL1, COL2, COL3, COL4

A, B, C, D

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

adamjiawen — Mon, 16 Jun 2014 08:03:35 GMT

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

Re: Mark a num on the repeating rows in script

Mon, 16 Jun 2014 08:15:38 GMT

BaseTable:

LOAD * INLINE [

COL1, COL2, COL3, HowToGetThis?

A, B, C, 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;