This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
If you are good at flipping around tables......got a q for you.
I have a table that looks like this:
The numbers after BUS,EMP,REV represent a "size"
I want to rearrange this to look like this:
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?
Go to Solution.
Go to Solution.
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.
I believe you can do this with Transpose, Regex (parse), and Crosstab:
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
Hope this helps
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.