topic Re: Unique ID Generation to Existing Database in Alteryx Designer Desktop Discussions

Unique ID Generation to Existing Database

knozawa — Thu, 24 May 2018 04:13:24 GMT

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:

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

This is the sample new data:

ID	Name
NEW1	GGG
NEW2	HHH
NEW3	III
NEW4	JJJ
NEW5	KKK
NEW6	LLL
1	AAA
2	BBB

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

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

I also attached a sample workflow.

Sincerely,

Kazumi

Re: Unique ID Generation to Existing Database

nick_schimweg — Fri, 15 Sep 2017 23:17:08 GMT

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.

Re: Unique ID Generation to Existing Database

NicoleJohnson — Fri, 15 Sep 2017 23:20:59 GMT

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! :)

Re: Unique ID Generation to Existing Database

knozawa — Sat, 16 Sep 2017 18:20:36 GMT

@nick_schimwegand @NicoleJohnson,

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

Sincerely,

Kazumi