I have a problem joining two of my dataset. One main problem is there is no primary key for the two dataset since there are duplicate records with both sets. I'm trying to get supplemental data from the Table 2 to the Table 1. Table 1: RecordDetails1 1 2 2 Table 2: RecordDetails1Detail 12Detail 22Detail 32Detail 43Detail 5 Result should be like this: RecordDetails1Detail 11 2Detail 22Detail 3 Thanks for the help.

@Rhael012 I agree with @Qiu and @apathetichell that Tile is a helpful tool on this type of problem. I did a test and you can just expand your unique and groupby selection to the keys that you want to join two tables on, i.e. Detail 1, Detail 2, and Transaction Date. See attached workflow.

One thing that I'm not sure is why from your desired result that you mapped Findings 3 not Findings 4. We can change the workflow based the logic why you want "Findings 3" assign to this record.

0408223

Account 2

Transaction 1

4/8/2022

Findings 3

New Workflow1.yxmd

All comments

Hi @Rhael012, I'm trying to understand your end goal. So although you have Detail 4 assigned to Record 2, because you only have 2 rows from table 1, your end results won't have the Detail 4?

Hi @Rhael012, based on the assumption that I made earlier, see attached workflow. You can use the multi-row to create a rank column that way, you have a unique composite key to join on, and then union it together to have the desired results.

New Workflow1.yxmd

Qiu

@Rhael012
Maybe we can use Tile tool to generate the primary key? 😁

0409-Rhael012.yxmd

Just chiming in here to support @Qiu 's suggestion of Tile tool. It's very helpful in creating fake primary keys to prevent expanding joins.

Qiu

@apathetichell
Thank you.
I also somehow like the Tile Tool. 😁

Hi @cathyshi525, to give context on what I'm trying to do. On a previous day, there are multiple transactions that is look alike. When those transactions has been reviewed, there will be additional details inputted on each of those transactions. What I'm trying to achieve is to get the details of those transaction from the previous day based on the current date transaction. There will be some transactions that was closed on the previous day. Thus, when we check the current day transactions, some of those transactions will not be generated from the system.

The logic is like this, get all transactions for this day, check each transactions if there is a previous record from the previous day. If there is, get the details from those transactions. Hope this helps to clarify what I'm trying to do. Thanks

Hi - This sounds like a self-join where you take the current day - create a field for previous day. And then do a self join where on current day/transaction id and previous day/transaction id. The resulting join anchor will be entities which had a matching reference yesterday.

Hi @Qiu, this solution looks promising. But I am not getting my desired output.

Let's say I have 20k records on Table 1 which may have duplicate records and I have 38k records on Table 2 which also have duplicate records.

Although Table2 has many records, I only need the output to be exactly how many records in Table 1.

The tricky part here, is even though some of the records are look alike since they don't have a primary key, each records are treated as unique because of the details that I'm trying to get from Table 2 to supplement the data on Table 1.

Hi @apathetichell ,Yes that's right. This is what I'm trying to do. By the way, how can I tag a user in my post? hehe. Sorry, I'm new here.

@Rhael012type @ and then choose the name from a dropdown. That's the easiest way. If you have some sample data with a date/transaction key - I can make you a mock worklow - or I'm sure @Qiu can when he wakes up.

@apathetichell, here is the data that I can share.

Current Day data:

UniqueKey	RecordID	Detail 1	Detail 2	Transaction Date	Findings
	1	Account 1	Transaction 1	4/8/2022
	2	Account 1	Transaction 1	4/8/2022
	3	Account 2	Transaction 1	4/11/2022
	4	Account 2	Transaction 2	4/11/2022
	5	Account 2	Transaction 1	4/8/2022
	6	Account 3	Transaction 1	4/11/2022
	7	Account 3	Transaction 2	4/11/2022
	8	Account 4	Transaction 1	4/8/2022
	9	Account 5	Transaction 1	4/8/2022
	10	Account 6	Transaction 1	4/8/2022

Previous Day Data:

UniqueKey	RecordID	Detail 1	Detail 2	Transaction Date	Findings
0408221	1	Account 1	Transaction 1	4/8/2022	Findings 1
0408222	2	Account 1	Transaction 1	4/8/2022	Findings 2
0408223	3	Account 2	Transaction 2	4/8/2022	Findings 3
0408224	4	Account 2	Transaction 1	4/8/2022	Findings 4
0408225	5	Account 3	Transaction 1	4/8/2022	Findings 5
0408226	6	Account 3	Transaction 2	4/8/2022	Findings 6
0408227	7	Account 3	Transaction 1	4/8/2022	Findings 7
0408228	8	Account 3	Transaction 2	4/8/2022	Findings 8
0408229	9	Account 4	Transaction 1	4/8/2022	Findings 9
04082210	10	Account 4	Transaction 1	4/8/2022	Findings 10
04082211	11	Account 5	Transaction 1	4/8/2022	Findings 11
04082212	12	Account 5	Transaction 1	4/8/2022	Findings 12
04082213	13	Account 6	Transaction 1	4/8/2022	Findings 13
04082214	14	Account 6	Transaction 2	4/8/2022	Findings 14
04082215	15	Account 6	Transaction 1	4/8/2022	Findings 15

Desired Result:

UniqueKey	RecordID	Detail 1	Detail 2	Transaction Date	Findings
0408221	1	Account 1	Transaction 1	4/8/2022	Findings 1
0408222	2	Account 1	Transaction 1	4/8/2022	Findings 2
	3	Account 2	Transaction 1	4/11/2022
	4	Account 2	Transaction 2	4/11/2022
0408223	5	Account 2	Transaction 1	4/8/2022	Findings 3
	6	Account 3	Transaction 1	4/11/2022
	7	Account 3	Transaction 2	4/11/2022
04082210	8	Account 4	Transaction 1	4/8/2022	Findings 9
04082211	9	Account 5	Transaction 1	4/8/2022	Findings 11
04082213	10	Account 6	Transaction 1	4/8/2022	Findings 13

From here, as you can see, for the current day, we can identify the transactions that is generated only this day. Thus, we also need to create a UniqueKey for the Transactions that has no UniqueKey.

Hope this will be helpful.

without a uniquekey in the current day dataset this won't work. You'll get hit with dimensional issues and get multiple entries for the same identifier.

let me explain - say you are trying to join account 1/transaction 1 from 4/8/2022 from current day - without a unique id it will not know which entry to match vs previous day - solution it will match both. This is standard SQL. Excel uses fake matching via vlookup (a match first strategy) but that is just an artificial excel strategy.

If you have unique transaction id in both this becomes straight forward.

One thing that I'm not sure is why from your desired result that you mapped Findings 3 not Findings 4. We can change the workflow based the logic why you want "Findings 3" assign to this record.

0408223

Account 2

Transaction 1

4/8/2022

Findings 3

New Workflow1.yxmd