topic Re: join two tables/file in Talend Studio

join two tables/file

Anonymous — Wed, 03 Oct 2018 22:20:16 GMT

Hi All,

I have one scenario, where I wanted to join two files/tables.

Input1: transaction details,

id|trans_date
3|9/3/2018
3|9/4/2018
3|9/5/2018
3|9/6/2018
3|9/7/2018
3|9/8/2018
3|9/9/2018
3|9/10/2018
3|9/11/2018
3|9/12/2018
3|9/13/2018
3|9/14/2018
3|9/15/2018
3|9/16/2018
3|9/17/2018
3|9/18/2018
3|9/19/2018
3|9/20/2018
3|9/21/2018
3|9/22/2018

Input 2: status

id|status|dt
3|active|9/3/2018
3|charge off|9/6/2018
3|close|9/20/2018

output should be:

id|trans_date|Status
3|9/3/2018 |active
3|9/4/2018 |active
3|9/5/2018 |active
3|9/6/2018 |charge
3|9/7/2018 |charge
3|9/8/2018 |charge
3|9/9/2018 |charge
3|9/10/2018|charge
3|9/11/2018|charge
3|9/12/2018|charge
3|9/13/2018|charge
3|9/14/2018|charge
3|9/15/2018|charge
3|9/16/2018|charge
3|9/17/2018|charge
3|9/18/2018|charge
3|9/19/2018|charge
3|9/20/2018|close
3|9/21/2018|close
3|9/22/2018|close

can someone help here.

Thanks,

Re: join two tables/file

manodwhb — Thu, 04 Oct 2018 06:23:20 GMT

@mailforsaggy,you do inner join with all match model and the in the expression you need to derive that by comparing the dates.

Re: join two tables/file

Alaor_Silva — Thu, 04 Oct 2018 12:51:21 GMT

Hi @mailforsaggy,

You need to do like this.

Then inside the tMAP

cheers

Re: join two tables/file

Anonymous — Thu, 04 Oct 2018 13:43:14 GMT

Hi @manodwhb,
I tried this thing but if I do inner join on id and date, then other records from from trasn table get dropped. if I joined only on id with all match model, then I am getting in output 60 rows instead of 20. and each row replicated thrice. but this is not what I want if you check the output.

Re: join two tables/file

Anonymous — Thu, 04 Oct 2018 13:44:37 GMT

Hi @nhalicka

if i join on id and date, then in the output I will get only 3 records, which is not the required output

Re: join two tables/file

Alaor_Silva — Thu, 04 Oct 2018 14:14:41 GMT

@mailforsaggy,

If we take a closer look at your data, the output that you want cannot be achieved.

We just have status for day 03, six and 20 the other days in your output is just made up data.

At the best, you can get is this:

The rest of that data you want is not existent to match.

Unless you have some criteria,

Dates between day 4 and 5 AND (status = null) should be equals "active"
Dates between day 7 and19 AND (status = null) should be equals "charge"
Dates between day 21 and 22 AND (status = null) should be equals "close"

This condition is just to make sure we will not mess up with good data ***AND (status = null)

Cheers

Re: join two tables/file

Anonymous — Thu, 01 Nov 2018 20:57:47 GMT

I am getting the same output. but which is not I wanted.