How to Lookup dates from a list of dates and bring in specific data

Four Stars

How to Lookup dates from a list of dates and bring in specific data

Hello, 
I have an issue that is preventing me from completing a critical project. I cannot figure out the correct way to go about this. 
I have to join my main data on Date Surveyed and Account Number to a file that contains Names that have changed over time. So the Date Surveyed has to fall within the range of the most recent change and the previous change in name. 
I have attached several snapshots to help paint the picture. 

I've been trying to figure this out in the tmap component, but am getting either errors or incorrect results.

Please help guide me to do this!

Employee

Re: How to Lookup dates from a list of dates and bring in specific data

Hi,

 

   I would suggest you to provide a sample set of data (after removing sensitive information) for your input main and lookup flows. You can provide 10 or 20 records which you hope is fine for testing. Please also provide the expected result set for this input data. 

 

   If there are any mapping rules which community members need to look during processing, please specify it also in the description. Then we will be able to verify the flow from our end by building some sample programs.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Four Stars

Re: How to Lookup dates from a list of dates and bring in specific data

Thank you so much for your reply.  I'm really struggling with how to set this up.

Here are sample sets you requested.

I'm trying to look up both the name and cert changes to the main file in the columns AI-AN.

I supplied the expected results based on the namechgfile and the certchgfile.

The lookups are based on the chg dates of the name or cert to the Date Surveyed of the main file.

 

Please let me know of anything else to help me solve this!

Thank you!

Employee

Re: How to Lookup dates from a list of dates and bring in specific data

Hi,

 

     First of all, apologies for getting back late as I was held up with my routine work and didn't get a chance to look to community stuff.

 

     I verified the files and have created the draft flow for name changes. The same can be applied for certificate change also as the underlying logic remain same. You will have to include two more subjobs to the existing flow for certificate change.

 

    The overall job diagram is as shown below.

 

image.png

 

The high level steps are as shown below.

 

a) The input file will be read and will be passed to a tMap as shown below. We will be adding a sequence to identify the row number for each row (which will be used later for de-dup process).image.png

 

b) The next step is to find all possible matches based on lookup name file. Once the lookup is complete, you will get both date values and do a difference between both values. The code is as shown below.

 

TalendDate.diffDate(out1.DATESURVEYED,row2.CHGDT,"dd") 

image.png

 

c) Replicate the data flow to two where first flow will capture the actual data and second flow will identify the record with minimum date difference.

image.png

 

d) Once the data is available, the next step is to merge them in another subjob to pick only the records which matches with aggregation values.

 

image.png

 

e) The output along with matching data will be printed in output console.

 

Note:- If there are no date changes on the day of survey, you will have to apply additional comparison with lookup table to pick the oldest record.

 

This method will give you a path forward to your final solution. Please make necessary amendments for your specific final use case.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

 

Employee

Re: How to Lookup dates from a list of dates and bring in specific data

Hi,

 

    I have also attached the job and the metadata schema for your quick reference in the attachment section.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Nine Stars

Re: How to Lookup dates from a list of dates and bring in specific data

Hi,

A specific column and date range join is actually a little bit tricky. Using a tMap range join, find the values that fall within the from/to dates of interest, feed these results to a file or hashmap.

RangeJoin.jpg

 

 

 

 

 

 

 

 

 

 

 

However anything it cant join will get dropped, so you need to join the results from the "tmap range join" back on to the original source so you still have the all of the values.

 

 

 

Regards David
Dont forget to give Kudos when an answer is helpful or mark the answer as the solution.
Four Stars

Re: How to Lookup dates from a list of dates and bring in specific data

Thank you so much for replying.  Let us take a look at this and let you know of any questions.

Thanks again!

Michelle

Four Stars

Re: How to Lookup dates from a list of dates and bring in specific data

Thank you for your reply!  We will look at your suggestion and let you know.

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads