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

Weird kind of transposing

CorCor
8 - Asteroid

 

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

 

 

 

 

4 REPLIES 4
CharlieS
17 - Castor
17 - Castor

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.

EW
11 - Bolide

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

transpose.PNG

 

nabandla
7 - 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

CorCor
8 - Asteroid

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