Dynamically Splitting Data in Rows into Columns based on a unique identifier

For once I can actually share my example file (alteryx help) because there's no protected info. Although I did dummy a few fields to be safe.

I have this incredibly long output file with over 200 columns. Each row is data for a different facility. Their Unique Identifier is in column 2.

I want to split this data out by identifier, and then hopefully transform it into more manageable chunks, but I also don't know how to do that. I attached an example of what I want it to look like as well. The example is limited, it obviously doesn't capture every field, but I just wanted to demonstrate how I wanted to break up the file into more manageable chunks.

The data I work with is usually cleaner, but this is the output I'm given. If anyone has a way to approach this, I'm all ears. Do I need to format my data before running it through the workflow somehow? Or can alteryx handle all of this so once the process is automated, we can just drag and drop these outputs and get the desired result?

AlteryxHelp.csv

eventualOutputExample.xlsx

Datasets

Output

Transformation

Dynamic Processing

Preparation

Optimization

Data Investigation

Accepted answers

All comments

Qiu

@WishIKnewHowToCode
Can you help me connect your input data with the output format?

What would be the "Value1", "Value2"...?
And what is "Line1" conresponding to your input data?

2024-05-08 172255.png

Raj

@WishIKnewHowToCode
please find attached
hope this helps.

1023.yxmd

WishIKnewHowToCode

Sorry, I probably oversimplified my examples. The below would be part of the example for Line 1 and values 1-5. L1 being line 1, and C2-6 being column 2-6. Since I'm not including column 1, 2-6 would be values 1-5 if that makes sense. This starts at Column AR in the excel file.

These are just values for Line 1, that need to be split out by facility as mentioned earlier. It's a pretty complicated workflow (to me anyway).

S-3 Part II L1.00 C2.00	S-3 Part II L1.00 C3.00	S-3 Part II L1.00 C4.00	S-3 Part II L1.00 C5.00	S-3 Part II L1.00 C6.00
57602612		57602612	1608593.4	35.81
30352798		30352798	813777.2	37.3
277981685		277981685	7716745	36.02
35086931		35086931	953681.7	36.79
30012921		30012921	696423	43.1
38815228		38815228	902070	43.03
71040289		71040289	2041193	34.8
392893666		392893666	10841594	36.24
95904801		95904801	2676610.6	35.83
34994609		34994609	956916	36.57
34661360		34661360	917102	37.79

WishIKnewHowToCode

The part I'm struggling with currently is how can I split out my facilities based on their unique identifiers without having to put a manual filter in for each facility? This will be used on many many different facilities and to add the filters in every time is a step I want to skip. Current workflow progress is attached.

AlteryxCommunityWorkflowHelp.yxmd

WishIKnewHowToCode

@Qiu Was any of what I provided helpful? Sorry I'm still stuck on this.

WishIKnewHowToCode

Also trying to figure out if maybe a Table tool is what I want so that I can arrange the data in manageable chunks

Qiu

@WishIKnewHowToCode
It seems that we are on the same track of starting point.

Though I used the Regex to parse the "Line" and "Column" and it appears to be duplicated "Lines".

0508-WishIKnewHowToCode.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703