Keep all records from Input1 and append only 1 record from Input2 to Input1 in output file

[Rumanais]

Hello,

I have been trying to get to a desired output data from 2 different data sources/files. I have tried Join  and then used Union tool to merge the data sets, but if I only do a union of the left join and the join the True anchor of the filter containing the condition, then the accounts in the Input1 where the condition is not met get dropped off. On the other hand, if I use an Union tool to bring both True and False anchor data from Input2, it brings in all the records from Input2 that I do not want in the output file. 

I have attached 2 excel file with Input1, Input2 and the Input  file containing the desired output.

The condition that needs to be applied is:  

If the PROC_TS in Input2 is >= ACCT_CLOSE_DT in Input1, then bring RWARD_QT and  PROC_TS from Input2 to Input1 in the output file

If not ( PROC_TS in Input2 is <ACCT_CLOSE_DT), then bring the RWARD_QT and  PROC_TS from Input2 where the  PROC_TS is closest to the ACCT_CLOSE_DT in Input1 

Any help would be greatly appreciated!

Thank you

[SPetrie · Accepted solution]

 Unless Im misunderstanding, I think you can get away with a join and then a sample.

Join everything by the ACCT and sort it by date, then just sample them grouping by the ACCT.

[Rumanais]

Hi SPetrie,

Thank you so much for the solution...it worked perfectly!! However, I did not realize there were another scenario that I needed to consider, which was, if there were no record in Input2 of an account in Input1, then I would still like the account to be displayed in the desired output.  I have modified the Dataset1 file with this scenario.  Do you think adding another join and a Union afterward may help in this situation?  Or there are other better way of doing it?

Thanks a bunch!

[SPetrie · Accepted solution]

Glad I was able to help!

For the new scenario, a union tool should be enough.

You add that in after the original Join and then connect it to the left output to grab any items that did not join with the Dataset2 items.

[Rumanais]

This Worked!!!! Thank you so much!!

[Rumanais]

Hi SPetrie,

I apologize! As I began to look closer to the result, I realized the solution 2 worked partially :(

For instance, out of 7 accounts listed in Dataset1 with the solution, it worked for 4 accounts and did not work for 3 accounts. the discrepancies are as follows (the details are in tab 'Actual Output_recordID_Dataset1):

The desired result should have been:

ACCT_NB	ACCT_CLOSE_DT	RWARD_QT	PROC_TS
12501AFGHD	2/13/2023	-217	03/16/2023 14:26:39
12506KLGHF	11/28/2022	-3,551	11/25/2022 08:11:06
12508OGFHR	1/31/2023	-53	01/25/2023 08:33:02

But the actual outcome was:

ACCT_NB	ACCT_CLOSE_DT	RWARD_QT	PROC_TS
12501AFGHD	2/13/2023	-3013	2/21/2020 12:14
12506KLGHF	11/28/2022	-30480	9/17/2018 11:01
12508OGFHR	1/31/2023	-10059	12/28/2019 14:39

I tried to update the workflow by switching the record ID to dataset2 and that reduced the discrepancy to two accounts (shown in the tab 'Actual Output_recordID_Dataset2') . I am not sure why this is happening and what might be the solution.  Hoping you could help.  I am attaching the datasets again (In Input2 tab: the lines highlighted in green is what is expected. The lines highlighted in yellow is what is being returned) for your reference. Any guidance you could provide would be greatly appreciated.

Thank you

[Rumanais]

Hi SPetrie,

I figured out the issue. the issue is actually in the data I am using :) thanks again for your help!!