"Joining" two different list without repetition of the elements of each one:

Hello all,

I'm trying to do something quite simple, but hard performance wise. The goal is to attribute a set of Codes to a group of clients, given these constraints:

  • Both the set of codes and clients have to be gathered given some business rules;
  • Each Client has to be attributed with an exclusive code – meaning: no repetition of codes between clients;
  • The table of clients have about 150k of records and the table of records have +1M.


My approach here, would be:

1. Compiling the list of Clients;

2. Compile the list of Codes;

3. “Join” both of them and do a bulk insert (as shown blow):


Client


Code

->

Code Attribution

Albert


AAA


Albert

AAA

Bruno


BBB


Bruno

BBB

Carolina


CCC


Carolina

CCC

Daniel


DDD


Daniel

DDD


However, my biggest issue is to “join” them this way, be it in SQL or even in a OS flow. Do you guys have any idea, or suggestion? Is something in the forge that might help me?

Thank you all!

Hi Telmo,

why do you want to create the lists separately?

When adding a new element to the List, you can assign the Client and the Code can be generated from a function.

Hi José,

In fact, I didn't want/need to create them separately. The intent was to get a match between to lists (X_N and Y_N) with no elements in common and get a new list (Z_N = {X_N, Y_N})  composed by the pairs of the first two.

From my first analysis, I haven’t got any ideas regarding a SQL approach, and that was when I created this post – hoping for a OS solution that would make that possible.

However, in a group discussion, we got the idea of doing it by SQL, distinguishing  each row by its ROW_NUM and joining them by that auxiliary property.

I have not tried it yet, but I think that it will solve the problem here presented. In any case, I’ll leave this forum open in case someone has other idea worthy of trying.

Thank you!

Champion

HI Telmo

Can you share more details? What is the structure of the Client and Record table now? 

What table has duplicate records? etc.

Kind regards,

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.