tMap Problem with NULL values on identic JOBS

Highlighted
Four Stars

tMap Problem with NULL values on identic JOBS

Hi everyone, I am completely new and am experiencing some problems joining 2 tables;

-Bank Transactions

-Currency rate

(only matching column being the Date)

 

When I create a job using those tables as delimited files the job runs successful

(Image 1 - a joined list is displayed showing NULL values only where there are no transactions on the specific date)

 

If I create an identical job with one source delimited and other MySQL I end up with all lookup values displayed as NULL (Image 2)

 

Join is identical in both jobs (Image 3)

Desktop Screenshot 2019.09.30 - 10.30.16.44.pngJOB 1 delimited filesDesktop Screenshot 2019.09.30 - 10.30.31.89.pngJOB 2 delimited file and MySQLDesktop Screenshot 2019.09.30 - 10.31.06.02.pngJOIN

 

Any advice is welcome

 

 

 

 

 

 


Accepted Solutions
Four Stars

Re: tMap Problem with NULL values on identic JOBS

I have managed to solve my issue.

Eventually I had changed schemas from both tables from repository to built-in

and changed date columns types to string !

 

Together with the all matching rows outer join results are now showing all trnsactions linked with both chosen currencies ! 

 

Desktop Screenshot 2019.09.30 - 14.33.46.96.png

 

Thank You for the helpful hints !


All Replies
Fifteen Stars TRF
Fifteen Stars

Re: tMap Problem with NULL values on identic JOBS

I'm afraid both "datum" fields used for the join are not expressed with the same format


TRF
Seventeen Stars

Re: tMap Problem with NULL values on identic JOBS

Hi, the format of the Date typed fields are mostly irrelevant because this pattern is only used when converting the Date typed field into text format of read it from text format. As long as you deal with the Date typed value you do not need the pattern.

You have to check if you have your date pattern fits to the text values in your input file.

And please check the join condition. You have chosen "Unique match" - means if the lookup has more than one records with the same date it will not match.

 

Four Stars

Re: tMap Problem with NULL values on identic JOBS

So I should have both columns converted to datetime for instance ?
Seventeen Stars

Re: tMap Problem with NULL values on identic JOBS

Convert them to Date typed values!

Four Stars

Re: tMap Problem with NULL values on identic JOBS

Ah I see matching all rows returned ALL matches for all dates and duplicate dates and all currencies.

 

I believe that actually I had used the Currency .csv in Jupyter Notebook and changed Datum column to datetime.. I will attempt another join with the original file.

Four Stars

Re: tMap Problem with NULL values on identic JOBS

I have managed to solve my issue.

Eventually I had changed schemas from both tables from repository to built-in

and changed date columns types to string !

 

Together with the all matching rows outer join results are now showing all trnsactions linked with both chosen currencies ! 

 

Desktop Screenshot 2019.09.30 - 14.33.46.96.png

 

Thank You for the helpful hints !

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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog