topic Re: Running sequence number while implementing SCD2 in Talend Studio

Running sequence number while implementing SCD2

sushantk19 — Sat, 16 Nov 2024 02:33:54 GMT

How to implement Running Sequence Number(Auto increasing) like Skey from SCD component.

Presently I have used the below logic:

Numeric.sequence("row1.id",1,1)

But this always start from 1 if i run my job again, causing duplicate in Skey values. Please suggest.

Re: Running sequence number while implementing SCD2

manodwhb — Wed, 29 Apr 2020 01:58:43 GMT

Use the one it will work ,what are you expecting.
Numeric.sequence("s1",1,1)

Re: Running sequence number while implementing SCD2

sushantk19 — Wed, 29 Apr 2020 12:02:41 GMT

@manodwhb : Thanks! I will check that.

By the way, I used the postgresqlSCD component and worked perfectly fine for my requirement! Is there any issue with this component for implementing SCD2?

Re: Running sequence number while implementing SCD2

manodwhb — Wed, 29 Apr 2020 14:21:30 GMT

@sushantk19, You can implement SCD2 with postgresqlSCD component.

Re: Running sequence number while implementing SCD2

sushantk19 — Tue, 05 May 2020 10:59:25 GMT

Yes, It works fine!

Re: Running sequence number while implementing SCD2

sushantk19 — Wed, 06 May 2020 11:09:04 GMT

@manodwhb I got a issue here. Every time i run my job, sequence again starts from 1 and not as max(row count) in table. As an eg if i have 300 records in table with above sequence logic, the next time i load the data, the sequence no should be 301 and NOT 1 which is happening now. Please suggest what needs to be done here.

Re: Running sequence number while implementing SCD2

manodwhb — Wed, 06 May 2020 11:26:44 GMT

@sushantk19,in your job first you should read max value from that table keep in context or global variable and then in the next flow in the same job you need to connect both using on subjob ok and then you have to use a sequence like below .

Numeric.sequence("s1",context.max_id,1)

Re: Running sequence number while implementing SCD2

sushantk19 — Fri, 26 Jun 2020 12:12:51 GMT

Hi,

Sorry for the late reply again. This logic is failing and i am getting some random value as the next value.

I tested the code today again with some more data from source. My max id =10. I got a new code from the source, so this new code should be inserted with the id as 11, but i am getting sometimes as 23 or sometimes as 28. Not sure what is happening. Please note my code field is string and ID is integer.

ID= Integer

code= string.

whenever, a new code is found, it should increment the ID by 1.

Present logic is ID=Numeric.sequence("s1",context.max_id,1), but its not working and giving me a random value as 23 or 28 after 10. please suggest what is going wrong here.

Re: Running sequence number while implementing SCD2

manodwhb — Fri, 26 Jun 2020 12:21:16 GMT

@sushantk19 , can you show your screenshot of mapping?

Re: Running sequence number while implementing SCD2

sushantk19 — Fri, 26 Jun 2020 12:25:36 GMT

@manodwhb : please find the screenshots attached.

extracting_source.png
mapping.png
max_id_defination_context.png

Re: Running sequence number while implementing SCD2

sushantk19 — Fri, 26 Jun 2020 13:25:09 GMT

Please ignore the below scenario. There was a bug in my code which has been corrected now.

The below condition is correct:

Numeric.sequence("s1",context.max_id,1)