Six Stars

Scd type 2 without using component

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
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

Re: Scd type 2 without using component

24 REPLIES
Nine Stars

Re: Scd type 2 without using component

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,

 

Veeru Boppudi
Six Stars

Re: Scd type 2 without using component

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
Eleven Stars

Re: Scd type 2 without using component

Six Stars

Re: Scd type 2 without using component

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

 

 

Thanks,

lmit

 

 

 

Four Stars

Re: Scd type 2 without using component

Hi limit,

 

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

 

Thanks,
Arpit

Six Stars

Re: Scd type 2 without using component

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

Four Stars

Re: Scd type 2 without using component

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
Eleven Stars

Re: Scd type 2 without using component

Six Stars

Re: Scd type 2 without using component

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

Four Stars

Re: Scd type 2 without using component

@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.

Four Stars

Re: Scd type 2 without using component


Thanks Lmit

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

Re: Scd type 2 without using component

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

Re: Scd type 2 without using component

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

gettting error on this:

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

mapping.PNG

Six Stars

Re: Scd type 2 without using component

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
Six Stars

Re: Scd type 2 without using component

Hi arpit, 

 

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

 

Thanks,

lmit

Four Stars

Re: Scd type 2 without using component

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?

var.PNGThanks,

Arpit

Six Stars

Re: Scd type 2 without using component

Hi Arpit,

 

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

 

Thanks,

lmit

Six Stars

Re: Scd type 2 without using component

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

Four Stars

Re: Scd type 2 without using component

Hi lmit,

 

Here are tables details

source

source tabre.PNG

 

target

Targettables.PNG

s_key is auto generated key and userid is primarykey.

 

Thanks,

Arpit

Six Stars

Re: Scd type 2 without using component

Hi arpit,

Strange mine was also int datatype only.can you try below one
!(varid== null)

Thanks,
Lmit
Community Manager

Re: Scd type 2 without using component

Hi arpiita
About the compile error, check the 'nullable' box of userid column in the row1 schema.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: Scd type 2 without using component

Still getting error

Job:

jobb.PNG

Tmap

save.PNG

Error;

opertor.PNG

 

Thanks,
Arpit

Four Stars

Re: Scd type 2 without using component

Can someone have look at this and tell me where i am going wrong?

please its urgent.

Moderator

Re: Scd type 2 without using component

Hello,

Did you put your filter in expression filter? Please use "||" instead of "&&" in your expression.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.