Four Stars

How to get start date for new record and set end date for previous record

Hi All,

 

I am implementating SCD type2 without using SCD component in talend but i am not getting Startdate and End as par my requirement.

Requirement is:

1.If data is fresh then start date will set as current date and end date as null.

2.if data is already exist in DB but one attribute changed that data then what it should do for previous record end date should be as current date and for new updated record start date as current date and end date will be null.data.PNGJob.PNGMap.PNGMap2update.PNG

Please Tell me where m going wrong.

 

Thanks,
Arpit

Here my screenshots:

20 REPLIES
Fifteen Stars

Re: How to get start date for new record and set end date for previous record

You shouldn't need 2 subjobs. Why not just have 1 subjob with your new data feeding your tMap as Main and your existing data (in the scd) as your lookup. Join on whatever key you use. When there is a join, you need to update your existing record (use an output to update this record setting the end date as the date of the new record. You also have an output for your new record (where you will set the start date as the same date as the end date of the updated record). If there is no join, you only send the new record. 

 

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

Hi @rhall_2_0

 

Thanks for your response, I go through the your previous comment and implement but i am getting Variable error.

Below is snapshot:

var1.PNG

Four Stars

Re: How to get start date for new record and set end date for previous record

Hi @shong please take a look at this.

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

Can you click on the Code tab (in the bottom left corner of your workspace window), then look for red rectangles and click on them to see the code in error. Give us a few examples of the errors.

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

please take look at this on error.

null.PNG

Four Stars

Re: How to get start date for new record and set end date for previous record

opertor.PNG
Now getting this error.

Four Stars

Re: How to get start date for new record and set end date for previous record

Hera are all details:

jobb.PNGopertor.PNGsave.PNG

 

Please tell me if i am using wrong expression as well and putting condition in expression builder box for update and old record.

 

Thanks,

Arpit

Four Stars

Re: How to get start date for new record and set end date for previous record

ok i am fix the variable problem but now job looks like stuck cause condition and expression.

can anyone have look on this:

jobbbb.PNGmapper.PNG

 

Thanks,

Arpit

Four Stars

Re: How to get start date for new record and set end date for previous record

these are snapshots for insert, update and old dim_table, check if action on data option:

insert.PNGolddd.PNGupdatee.PNG

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

Your tMap should be checking if row7 data is null. The userId from row6 will not be null.....because that is Main data. You need to check the lookup data for nulls. If you get an error saying that int cannot be null, that is true. Just tick the "Nullable" tick box for that column in the schema and it will change it to an Integer. An int is primitive and can never be null. An Integer is a class and can be null.

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

still its not giving me any result. nulll.PNG

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

You're still checking for 0 instead of null. row7 values will be null if there is no join

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

If i am putting Null then it will give me erroropertor.PNG

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

You need to ensure your userId column is set to Nullable for row7.  

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

Please have a look on this

SSSSS.PNG

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

If you are still getting a compilation error you need to take another look at the Code tab to look for errors. Take a screenshot of the errors

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

still getting error on typemismatchconvert.PNGvariable.PNG

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

It looks like you have changed the job and the filtering for your tMap outputs. It says you are mixing int with booleans in the error message.

Rilhia Solutions
Four Stars

Re: How to get start date for new record and set end date for previous record

can you just clarify me which type expression i ll write for :

insert new record

updated record

inactive record

Fifteen Stars

Re: How to get start date for new record and set end date for previous record

You should understand that logic. At the moment you have a compilation error which needs to be resolved. Did you resolve the compilation error?

Rilhia Solutions