Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

SCD type 2 logic but not using SCD component. Any suggestions?

Hi there,

 

I'm loading a csv file that consists of list of zipcodes that has been downloaded from the Internet.  My keys are combination of Zipcode, City, State and Longitude.  I would load it in Postgres Database.  The requirement is to compare if these combinations already exists, check if there are changes, If there is, insert a new record of the recent changes and marked the old record as expired.  Otherwise, if the combination doesn't exists yet, insert the record and marked it as a new record.  I believe this is SCD type 2.  As much as I want to use the SCD component, I'm afraid I'm not familiar with it and its behavior. 

 

Any suggestion how can I implement this?

 

Thanks in advance!

Labels (4)
5 Replies
Anonymous
Not applicable
Author

Implemenation of SCD is very simple in Talned

check below :

http://dwteam.in/scd-type2-talend-open-studio/

 

The logic that u need to build with out SCD also simple with tmap. 

(source--main) tmap (target--lookup) ---> use inner join

take 3 out puts

1. matched records (one of these matched becomes existing record(use insert) with end date as -1 or high end value)

2. matched records(one of these matched becomes existingrecord(use update) with end date as startdate-1 )

3. un matched records(with inner join rejected link) --- will become new records(insert them to db)

 

let me know if you need more details

Anonymous
Not applicable
Author

Thank you for your response.  

 

I tried once again the SCD component.  I have source keys with the combination of city, longitude, zipcode and state.  I also created the following columns in my table:  end_dtae, is_active, sg_key and start_date.  I have type 0 fields and type 2 fields form my table as well.  When I put sg_key as surrogate keys and selected table max+1 as creation, it gets me an error.  But when I put the sg_key as source keys, it processed.  Also, I tried to modify the state and load again, the first record still has the end_date as 2099, it should be the run date of the second record? and the is_active flag of the first record, still is true.  

 

Any thoughts?

 

 


scd2.JPG
Anonymous
Not applicable
Author

@mj_00  if your source keys are(basically primary key of your table)  combination of city, longitude, zipcode and state then you need to select all of them in "SOURCE KEY" section 

and if state is part of your primary key and if you modify it then it never updates the existing record because it considers that record as new record only (so this will be inserted as new record)

surrogate key is a natural key which always has unique value ...

 

don't forget to give kudos if that helps you

Anonymous
Not applicable
Author

Check this vedio for full understanding of type 2 : https://www.youtube.com/watch?v=dH567IFszu4