How to set Unique value for a set of records

[Naren12345babu]

Hi,

I have n number of records in source database. Now for every 500 records extracted from source, I should be able to assign a unique value for the whole set, and for next 500 records , I should be able to assign another unique value and so on.

here , we are not given source count. How can I achieve this ?

detail sample example :

ID
A
B
C
D
E
F
G
H
I
J
K
L
M
N

Now for first 5 records extracted, I want to assign '1'
For next 5 records I want to assign '2'
...

Output

ID,UNQVAL
A,1
B,1
C,1
D,1
E,1
F,2
G,2
H,2
I,2
J,2
K,2
L,3
M,3
N,3

Now the number of source records are unknown...
How can i achieve this ?

[chulett]

Not sure how much help you need on this... a gentle nudge in the right direction?

Stage variables in a transformer controlling both the set count and doing "group change detection". On the first record in the group increment the counter, for the rest of the group use the current value. And in your case a group is certain number of records, so one approach would be to count the records and use the Mod() function to determine when you hit each occurrence of the Nth record. Also, be aware of how partitioning will affect all of this.

[Naren12345babu]

Hi,

I have used below solution,

I have created a serial number for each record from source. such as

ID, COUNTER
A, 1
B, 2
C, 3
D, 4
E, 5
F, 6
G,7
H,8
I,9
J,10
K,11
L,12
M,13
N,14
O,15
F,16

Now, I because I want to make set of 5 records , I have divided COUNTER with 5. When I divide , the integer of quotient will be 0 from 1 to 4, 1 for 5 to 9, 2 for 10 to 14, 3 for 15 and 16.

Now, I have used this integer part of the quotient to create Unique Key

so the output is

ID, COUNTER, UNQKEY
A, 1 , 1
B, 2 , 1
C, 3 , 1
D, 4 , 1
E, 5 , 2
F, 6 , 2
G, 7 , 2
H, 8 , 2
I, 9 , 2
J, 10, 3
K, 11, 3
L, 12, 3
M, 13, 3
N, 14, 3
O, 15, 5
F, 16, 5