Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank you very much for your help Manish,The function Peek is really a useful function.It's a good lesson to me.3Q~
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;