topic Re: Multiple rows with unique ID, need to pull information in one row by ID in Alteryx Designer Desktop Discussions

Multiple rows with unique ID, need to pull information in one row by ID

ctk — Wed, 07 Aug 2019 16:12:44 GMT

I have a file with multiple values that I need to combine on one line. For instance, I have a unique ID on multiple rows that need to be combined into one row.

Current format
ID	Serial Number	Install Date
123	AB112	1/2/2019
123	AB111	6/5/2018

Desired Format
ID	Serial Number 1	Install Date #1	Serial Number 2	Install Date #2
123	AB112	1/2/2019	AB111	6/5/2018

*Note: The original file contains more than 20 columns but I only need information from a few of the columns.

Any help would be much appreciated!

Re: Multiple rows with unique ID, need to pull information in one row by ID

BrandonB — Wed, 07 Aug 2019 16:25:48 GMT

Example solution attached

Re: Multiple rows with unique ID, need to pull information in one row by ID

ctk — Wed, 07 Aug 2019 16:40:14 GMT

Thanks! I'm just getting caught up at the formula. For example, I've input [Name]+"Serial Number"+ToString([RecordID]) and get the error The field "" is not contained in the record (Expression #1)

Re: Multiple rows with unique ID, need to pull information in one row by ID

BrandonB — Wed, 07 Aug 2019 16:51:50 GMT

Don't forget to choose what column you are creating or updating in the formula tool. Click the "Select Column" dropdown and either pick the column to update or add column and create a new one.

Re: Multiple rows with unique ID, need to pull information in one row by ID

ctk — Wed, 07 Aug 2019 17:22:11 GMT

I'm getting close. How do I get the serial number, install date, etc. to display the actual number and date without the word "Serial Number" and "Capital Install Date" beside the number? If there are multiple serial numbers and install dates, how do I get them to display in different columns? Thanks so much for your help!

Re: Multiple rows with unique ID, need to pull information in one row by ID

BrandonB — Mon, 12 Aug 2019 17:12:40 GMT

Great job so far! Normally removing the words would be as easy as just saying Replace([Serial_Number],"Serial Number", "") in a formula tool and it would remove the words just leaving the numbers. The added complexity comes from the fact that you could have multiple Serial Numbers in a given column. Do you happen to know what the maximum number would be? I'm thinking that you could use a Regex tool like the attached workflow to pull them out. Depending on the maximum number of serial numbers that can exist in the column, you can increase the number of columns in the RegEx tool accordingly: