Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Unique ID Generation to Existing Database

knozawa
11 - Bolide

Hello,

 

I would like to add new IDs into existing database.  I would like to skip existing IDs and add new IDs in the name alphabetical order.

 

Here is the sample existing database:

IDName
1AAA
2BBB
3CCC
7DDD
10EEE
11FFF

 

This is the sample new data:

IDName
NEW1GGG
NEW2HHH
NEW3 III
NEW4    JJJ
NEW5    KKK
NEW6    LLL
1AAA
2BBB

 

The desired output is this: (new IDs are added as 4, 5, 6, 8, 9, 12)

IDName
1AAA
2BBB
3CCC
4GGG
5HHH
6III
7DDD
8JJJ
9KKK
10EEE
11FFF
12LLL

 

I also attached a sample workflow.

 

Sincerely,

Kazumi

3 REPLIES 3
nick_schimweg
8 - Asteroid

Hi Knozawa,

 

I wasn't able to download your workflow. Please take a look at the workflow I've attached, let me know if it makes sense. As always, I'm sure there is a better way to do it however this was the first thing that came to mind.

NicoleJohnson
ACE Emeritus
ACE Emeritus

Take a look at the attached, I believe this achieves what you are looking for?

 

1. Find matching Names

2. In the existing database, find the "gaps" by using the Generate Rows tool to find all ID's between the minimum and the maximum, then join to the existing database to find which ones do not exist in the existing database.

3. Join by Position to the new data to match however many records are needed to fill gaps in the existing ID's.

4. Union to matching names to create a list of existing plus new between the original minimum & maximum ID's, then sort ascending.

5. Union to any remaining ID's from the new data set, and then use the Multi-Row tool to generate new ID's for remaining records.

 

Let me know if this works or if you have any questions! :)

 

NJ

knozawa
11 - Bolide

@nick_schimwegand @NicoleJohnson,

 

Thank you very much for sharing your method!  Both workflows work great! 

 

Sincerely,

Kazumi

Labels