Weird kind of transposing
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If you are good at flipping around tables......got a q for you.
I have a table that looks like this:
SICGP | BUS1 | EMP1 | REV1 | BUS2 | EMP2 | REV2 | BUS3 | EMP3 | REV3 |
100 | 0.865 | 0.008 | 0.518 | 0.390 | 0.003 | 0.901 | 0.101 | 0.528 | 0.705 |
200 | 0.353 | 0.971 | 0.187 | 0.562 | 0.675 | 0.882 | 0.305 | 0.642 | 0.966 |
300 | 0.637 | 0.841 | 0.986 | 0.712 | 0.385 | 0.810 | 0.056 | 0.770 | 0.329 |
400 | 0.708 | 0.053 | 0.773 | 0.658 | 0.339 | 0.059 | 0.999 | 0.463 | 0.998 |
500 | 0.662 | 0.124 | 0.077 | 0.164 | 0.292 | 0.494 | 0.590 | 0.616 | 0.762 |
The numbers after BUS,EMP,REV represent a "size"
I want to rearrange this to look like this:
SICGP | SIZE | BUS | EMP | REV |
100 | 1 | 0.865 | 0.008 | 0.518 |
100 | 2 | 0.390 | 0.003 | 0.901 |
100 | 3 | 0.101 | 0.528 | 0.705 |
200 | 1 | 0.353 | 0.971 | 0.187 |
200 | 2 | 0.187 | 0.562 | 0.675 |
200 | 3 | 0.305 | 0.642 | 0.966 |
300 | 1 | 0.637 | 0.841 | 0.986 |
300 | 2 | 0.986 | 0.712 | 0.385 |
300 | 3 | 0.056 | 0.770 | 0.329 |
400 | 1 | 0.708 | 0.053 | 0.773 |
400 | 2 | 0.773 | 0.658 | 0.339 |
400 | 3 | 0.999 | 0.463 | 0.998 |
500 | 1 | 0.662 | 0.124 | 0.077 |
500 | 2 | 0.077 | 0.164 | 0.292 |
500 | 3 | 0.590 | 0.616 | 0.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
Solved! Go to Solution.
- Labels:
- Transformation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
