community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Weird kind of transposing

Highlighted
Meteor

 

If you are good at flipping around tables......got a q for you.

 

I have a table that looks like this:

SICGPBUS1EMP1REV1BUS2EMP2REV2BUS3EMP3REV3
1000.8650.0080.5180.3900.0030.9010.1010.5280.705
2000.3530.9710.1870.5620.6750.8820.3050.6420.966
3000.6370.8410.9860.7120.3850.8100.0560.7700.329
4000.7080.0530.7730.6580.3390.0590.9990.4630.998
5000.6620.1240.0770.1640.2920.4940.5900.6160.762

 

The numbers after BUS,EMP,REV represent a "size"

 

I want to rearrange this to look like this:

SICGPSIZEBUSEMPREV
10010.8650.0080.518
10020.3900.0030.901
10030.1010.5280.705
20010.3530.9710.187
20020.1870.5620.675
20030.3050.6420.966
30010.6370.8410.986
30020.9860.7120.385
30030.0560.7700.329
40010.7080.0530.773
40020.7730.6580.339
40030.9990.4630.998
50010.6620.1240.077
50020.0770.1640.292
50030.5900.6160.762

 

So basically - I am grouping the data by SICGP, but then want to stack the BUS, EMP, and REV info on top of each other per SICGP.

 

Got any ideas?

 

Thanks

Corey

 

 

 

 

Alteryx Certified Partner

There's a couple of steps to this. Check out the attached workflow to see one way to accomplish this. 

 

- Transpose to get everything into name/value pairs.

- Use the [Name] field to parse out two fields: numeric characters as [Size] and non-numeric characters as the new [Name] with the following formulas:

 

[Size] = tonumber(REGEX_Replace([Name],"[^\d]",""))

[Name] = REGEX_Replace([Name],"[\d]","")

 

- Cross Tab using SICGP and Size as the grouping fields then Name/Value pairs to the desired output.

Asteroid

I believe you can do this with Transpose, Regex (parse), and Crosstab:

transpose.PNG

 

Meteor

It can be done by  transposing and extracting the numbers and string from the name field find the workflow. For easy understanding i breakdown the each step in to one Tool

 

Result:

clipboard_image_0.png

 

Hope this helps

BR,

NB

Meteor

Tried this one first - and it mostly worked.  

Of course, I posted a sample with only 3 "sizes" - but the real data has 17.  And when I applied this solution to the real dataset....it cut off all the sizes at "9" - would not give me 10-17.

 

I am guessing it is a function of me not supplying the TOTAL use case.

 

Corey

Labels