Problem in Fetching appropriate Surrogate Key from diemsion table

One Star

Problem in Fetching appropriate Surrogate Key from diemsion table

Hi ,
 
        I have input data like below
 
        0,2523758,423010100393820,2014-05-03 04:37:51,01303000,3501020,2482515666,6586190617,1334,10,1334,10,658,0,144,0,2014-05-03 04:40:58,658.0,2,@@@@@@@@@@@@@@@@,2000000000000020,954,55.0,0.0,16,55.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,1,1266,6,I,658,4010,IND,SINGAPOR,55.00,,658,55.00,,0.00,,,PO
 
    Highlighted part represents mobile number and Transaction datetime
 
    Above record i need to populate in a fact table by fetching surrogate key for 2523758 for the date 2014-05-03 04:40:58
 
    Dimensional table data is as follows
 
    Key ,    Mobile number ,     SCD start date ,        SCD end date
    1234        2523758            2014-05-01 00:00:00    2014-05-02 00:00:00
    33236      2523758            2014-05-02 00:00:00    2014-08-19 00:00:00
    130978    2523758            2014-08-19 00:00:00    9999-12-31 23:59:59
 
     2014-05-03 04:40:58 lies between 2014-05-02 00:00:00  and 2014-08-19 00:00:00 .. so surrogate key for above record is 33236
 
 

    Joined above 2 files by using condition as shown in figure 1 and executed the job  but it fetched NULL surrogate key


   Same job i executed by interchanging rows 2 & 3 in dimension data as below then it fetched proper surrogate key 33236
 

  Key ,    Mobile number ,     SCD start date ,        SCD end date
    1234        2523758            2014-05-01 00:00:00    2014-05-02 00:00:00
    130978    2523758            2014-08-19 00:00:00    9999-12-31 23:59:59
    33236      2523758            2014-05-02 00:00:00    2014-08-19 00:00:00
  
Four Stars

Re: Problem in Fetching appropriate Surrogate Key from diemsion table

Hi,
Try following..
- Use tMap - lookup your dimension table with input data as main
- Use left outer join and get all the matches with the mobile number
- Store transaction datetime in context variable from main using tjavarow
- use tjavarow after tmap and use if-then-else loop to extract appropriate date from lookup table

I hope you got an idea.

Vaibhav

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog