How to generate serial number

Prince_Hyd · Post by **Prince_Hyd** » Sun Jun 30, 2013 1:01 am

Hi Folks

i have scenario in which two sources are the assume s1,s2 for s1 i have sno column but where as s2 sno is not there but i have to combine both those records and sno should continue from s1 to s2.

Thanks

ArndW · Post by **ArndW** » Sun Jun 30, 2013 4:05 am

If your s1 has 2 records with serial numbers 1 & 2, what should the serial numbers of records 1 and 2 in s2 be?

Prince_Hyd · Post by **Prince_Hyd** » Sun Jun 30, 2013 5:57 am

Hi Andrw

It's not like that see

S1
sno,deptno
1,10
2,10
3,20
4,30

and

S2
deptno
10
40
50

now i need output like

S-out
sid,deptno
1,10
2,10
3,20
4,30
5,10
6,40
7,50

chulett · Post by **chulett** » Sun Jun 30, 2013 8:02 am

Sorry but that just looks like math to me... as in "add one". What's the actual problem you're having here - how to combine the two sources in a serial fashion? What have you tried? It would help if you let us know what your sources actually are... files or tables.

Prince_Hyd · Post by **Prince_Hyd** » Sun Jun 30, 2013 9:50 am

Hi chullet
From the files you can see that S1 has sno's and S2 won't.Those are flat-files and i want to combine them in a serial fashion.

chulett · Post by **chulett** » Sun Jun 30, 2013 10:23 am

Of course I can see that, I'm just trying to say that the 'serial number' part of your post looks very straight forward to me. You should be able to use stage variables to track the records with a number and then add one after that for the ones that do not. As for the 'serial' part, I would look at the Filter option in the Sequential File stage to cat them together. You could recognize one from the other by the comma... or lack thereof.

Run on a single node or in sequential mode. Or a Server job would make this even easier.

mobashshar · Post by **mobashshar** » Sun Jun 30, 2013 3:23 pm

Hi Criag,
Just wondering how to use stage variables to track the records with a number and add one after that for ones that do not?
Just wondering.
Thanks.

ray.wurlod · Post by **ray.wurlod** » Sun Jun 30, 2013 4:08 pm

Only if your Transformer stage is executing in sequential mode. Otherwise the unqualified +1 approach is pretty much guaranteed to generate duplicates.

Depending on your target there are other strategies you can employ, such as a generated Surrogate Key or a database sequence.

chulett · Post by **chulett** » Sun Jun 30, 2013 9:16 pm

mobashshar wrote:Just wondering how to use stage variables to track the records with a number and add one after that for ones that do not?

I would check for a null v. non-null sequence number. Non-null, store it. Once you hit the nulls, add 1 to the previously stored value. And as noted twice now, do this in sequential mode so you have full control over the order the records come in.

Prince_Hyd · Post by **Prince_Hyd** » Sun Jun 30, 2013 11:31 pm

Hi ray

Actually i'm at beginner level .....i think we cannot connect multiple sources to transformer in that case how come your logic works.

Thanks