join two tables/file

[Anonymous]

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,

[manodwhb]

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

[Alaor_Silva]

Hi @mailforsaggy, 

You need to do like this.

Then inside the tMAP

cheers

[Anonymous]

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.

[Anonymous]

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

[Alaor_Silva]

@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

[Anonymous]

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