Complex Join

[Rhael012]

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:

Record	Details
1
1
2
2

Table 2:

Record	Details
1	Detail 1
2	Detail 2
2	Detail 3
2	Detail 4
3	Detail 5

Result should be like this:

Record	Details
1	Detail 1
1
2	Detail 2
2	Detail 3

Thanks for the help.

[CathyS_Slalom]

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?

[CathyS_Slalom]

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. 

[Qiu]

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

[apathetichell]

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. 😁

[Rhael012]

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

[apathetichell]

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.

[Rhael012]

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. 

[Rhael012]

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.