topic Re: Scd type 2 without using component in Talend Studio

Scd type 2 without using component

lmit — Sat, 16 Nov 2024 08:35:10 GMT

Hi all,

How can I implement scd type 2 without using scd type 2 components?

For sample
Id,name,course,key
1,xxx,jjj,k1
2,yyy,KKK,k2

For first load data will be loaded as its is.for second load if data is like below
Id,name,course,key
1,ccc,jjj,
3,ddd,lll

My output should be

Id,name,course,key
1,xxx,jjj,n
2,yyy,KKK,k2
1,ccc,jjj,y

The key column for old record should change to n and new one should be y

Any help would be helpful.

Thanks in advance,
Lmit

Re: Scd type 2 without using component

vboppudi — Thu, 08 Mar 2018 21:35:47 GMT

Hi lmit,

what about "3,ddd,lll " this record in second run? Do we need to load to target or reject?

What is the key value for new records is it 'Y' or null when inserting first time.

Regards,

Re: Scd type 2 without using component

lmit — Thu, 08 Mar 2018 23:55:56 GMT

Hi Veeranjaneyulu,

Thanks for your reply

3,ddd,lll
Will insert as a new record and key will be y. It will be empty while inserting for the first time.

Thanks,
Lmit

Re: Scd type 2 without using component

manodwhb — Fri, 09 Mar 2018 04:27:25 GMT

please find the below links.

http://www.deepinopensource.com/scd-2-implementations-in-talend/

http://talenddev.blogspot.in/2016/03/apply-scd-without-using-scd-component.html

Re: Scd type 2 without using component

lmit — Wed, 14 Mar 2018 03:37:47 GMT

Thanks Veeranjaneyulu and Manohar for your inputs.I achieved SCD type2 in other way

Thanks,

lmit

Re: Scd type 2 without using component

Anonymous — Tue, 27 Mar 2018 08:37:44 GMT

Hi limit,

Can you please explain how can you achieve SCD2 without using SCD compoenent.

Thanks,
Arpit

Re: Scd type 2 without using component

lmit — Tue, 27 Mar 2018 10:18:34 GMT

Hi Arpit,

I implemented using tmap with 3 flows ...

In first flow : i have compare the columns on which columns we need to implement the scd and lookup on one unique(Primary key) cloumn

1)if the id(lookup) had match and compare on any compared columns failes it should go to update flow(flag will change)

2)if the id(lookup) didnot match , then that record should go to insert flow(Flag will change)

3)if the id(lookup) and compare columns matched, then the record will go to ingore flow

according to my requirement i need to change the flag.

What is your requirement , do you need to generate key for update records ?

Thanks,

lmit

Re: Scd type 2 without using component

Anonymous — Tue, 27 Mar 2018 14:16:16 GMT

Hi Lmit,

Thanks for giving your response.
My requirement is:
1. if data is fresh it will load DB and generate Start date as current data and End date as null.
2. if data is already exist in DB but one attribute change then it will generate End date for previous record and insert new update record in DB with Start date as current date and End date as null.

Lmit, can you please share me screen shot of your Job and tmap that how you put expression on expression builder and what had you wrote expression for updateflow if data is already present in DB.

Hope you will understand my requirement.

Thanks,
Arpit

Re: Scd type 2 without using component

manodwhb — Wed, 28 Mar 2018 07:58:00 GMT

@arpiita, check the below link might be usefull for your case.

https://community.talend.com/t5/Design-and-Development/Flag-not-updating-based-on-condition/m-p/26823#M5082

Re: Scd type 2 without using component

lmit — Wed, 28 Mar 2018 15:25:43 GMT

Hi arpit,

Sorry for the last reply . please find the attachment for the screenshoots , follow the names of the screenshoots.

Hope it will be helpful for you to build the job.

Thanks,

lmit

SCD2.zip

Re: Scd type 2 without using component

Anonymous — Wed, 28 Mar 2018 15:59:50 GMT

@lmit Thanks Lmit for your renspose sure it will help me if you will show variable expression so that it will easy for me to write expression.

Re: Scd type 2 without using component

Anonymous — Wed, 28 Mar 2018 16:22:44 GMT

Thanks Lmit

which type of flag you used in implementation? Can you please explain me more so that it can help me.

Re: Scd type 2 without using component

Anonymous — Wed, 28 Mar 2018 16:27:10 GMT

If you don't mind can you send me screenshot of tmap.

Re: Scd type 2 without using component

Anonymous — Wed, 28 Mar 2018 16:55:46 GMT

(row1.column!= null)? 1:0

gettting error on this:

the operator != is undefined for the argument type(s) int

Re: Scd type 2 without using component

lmit — Wed, 28 Mar 2018 17:24:09 GMT

If am not wrong you are asking about local variables.
See the scd2_step2.jpg for the variable logic below the screenshot I have mentioned.

Thanks,
Lmit

Re: Scd type 2 without using component

lmit — Fri, 30 Mar 2018 04:05:53 GMT

Hi arpit,

are you able to implement SCD type2 or need more information?

Thanks,

lmit

Re: Scd type 2 without using component

Anonymous — Fri, 30 Mar 2018 07:19:54 GMT

Hi Lmit,

Thanks a lot for response. I am still not able to implement SCD type getting error on variable and condition, could you please help me in variables?

Thanks,

Arpit

Re: Scd type 2 without using component

lmit — Fri, 30 Mar 2018 07:56:48 GMT

Hi Arpit,

Can you please provide more information about error like on which columns you applied the condition and the datatypes?

Thanks,

lmit

Re: Scd type 2 without using component

lmit — Fri, 30 Mar 2018 08:23:13 GMT

i think your userId column is int datatype that's why its not taking considering !=

scd2_Step2.jpg in the Zip file which i send has the logic for the Variables.

Thanks,

lmit

Re: Scd type 2 without using component

Anonymous — Fri, 30 Mar 2018 15:29:34 GMT

Hi lmit,

Here are tables details

source

target

s_key is auto generated key and userid is primarykey.

Thanks,

Arpit