Hi,
I'm hoping that this is a simple operation, but I can't find the right combination of tools in Alteryx to transform my data approriately...
I'm generating a "summary" table (containing ~90 short columns) by extracting data from a workflow containing pension benefits. This table is then entered into an Excel front end for downstream processing in another application.
I've managed to extact the records from the data and have reduced it to a table with ~ 90 unique rows in the form:
Category | Tranch | incIndexTable |
Cat10A | Post 88 Post 89 GMP | CPI_0_3 |
Cat10B | Post 88 Post 89 GMP | Fixed_4 |
Cat11 | Post 88 Post 89 GMP | RPI_0_5 |
Cat11A | Post 88 Post 89 GMP | Fixed_4 |
Cat11B | Post 88 Post 89 GMP | Fixed_4 |
Cat11C | Post 88 Pre 89 GMP | Fixed_3 |
Cat11D | Post 88 Pre 89 GMP | Fixed_3 |
However, I need to tranpose so that the rows become columns, i.e.:
Category | Cat10A | Cat10B | Cat11 | Cat11A | Cat11B | Cat11C | Cat11D |
Tranch | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Pre 89 GMP | Post 88 Pre 89 GMP |
incIndexTable | CPI_0_3 | Fixed_4 | RPI_0_5 | Fixed_4 | Fixed_4 | Fixed_3 | Fixed_3 |
In Excel, I could do this by a simple Copy and Paste¦Transpose, so I'm certain there will be a simple way to do this in Alteryx too.
I've tried using the each of the Arrange, Transpose and Cross Tab tools, but can't see a way to configure any of them appropriately.
Thanks in advance.
Solved! Go to Solution.
Please see below :
1- When you read the data please check 'First row contains data'
2- I added a comment for each step
Attached the workflow.
Hope this helps!
Regards
Hi @alexnajm,
Thanks for the sugggestion. I think this would work if my data was as simple as the example. However when I apply to the actual data the "concatenation" approach in the cross tab causes isses. Although the Tranche¦Category¦Index combinations are unique, there are duplicate values in each of the columns, so these are joined together rather than staying as seperate columns..
Name | _Null_ | Cat10A | Cat10B |
Tranch | Non-Inc,Post 88 Post 89 GMP,Post 88 Pre 89 GMP,Post 97 3G,Post 97 4G,Post 97 Excess,Pre 88 GMP,Pre 89 XS,Pre 97 3G,Pre 97 4G,89-97 XS | Non-Inc,Post 88 Post 89 GMP,Post 88 Pre 89 GMP,Post 97 3G,Post 97 4G,Post 97 Excess,Pre 88 GMP,Pre 89 XS,Pre 97 3G,Pre 97 4G,89-97 XS | |
incIndexTable | Fixed_0,CPI_0_3,CPI_0_3,RPI_3_5,RPI_4_5,RPI_3_5,Fixed_0,Fixed_3,Fixed_3,Fixed_4,Fixed_3 | Fixed_0,Fixed_4,Fixed_4,RPI_3_5,RPI_4_5,RPI_4_5,Fixed_4,Fixed_4,Fixed_3,Fixed_4,Fixed_4 |
Hi @messi007,
Thanks for your suggestion, it almost worked, but was enough for me to adapt to get a working solution!
I'm not able to use the import approach you suggested as the starting data table is mid-workflow ratehr than being a seperate input table.
In the real data (as per my reply to @alexnajm), there are multiple occurances of the categories in the data that are needed the output, but causes an issue with the "all columns must have a unique name" in Alteryx.
This means that the output at the end of Container 42 becomes:
Field_90 | Cat10A | Cat10B | Cat11 | Cat11A | Cat11B | Cat11C | Cat11D | CatFMX | Cat10A2 | Cat10B2 | Cat11_2 | Cat11A2 |
Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | |
Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | CPI_0_3 | Fixed_4 | RPI_0_5 | Fixed_4 |
Additional suffixes are added to the column names to ensure all columns are unique.
I've managed to solve this by adding a couple of additional tools in a replacement container.
Using the output from container 44, I've added a
This gives the data in a format that users can then put into the downstream tools (when ignoring the header row!).
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
Category | Cat10A | Cat10B | Cat11 | Cat11A | Cat11B | Cat11C | Cat11D | CatFMX | Cat10A | Cat10B | Cat11 | Cat11A |
Tranche | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Non-Inc | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP | Post 88 Post 89 GMP |
incIndexTable | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | Fixed_0 | CPI_0_3 | Fixed_4 | RPI_0_5 | Fixed_4 |
Thanks for both of your help on this.
Hi @messi007,
I added the tools into my workflow and ran it to get the table I needed as per my post (and then forgot to save the workflow!!!)
As I was tidying up the workflow I've managed to "break" the cross-tab in container 40...
Even downloading your workflow again I'm not able to restore or replicate the solution again as I can't configure the cross-tab tool properly.
What configuration do I need in the cross-tab in Container 40?
I keep getting the error: "The field "NewField" is missing. Compare the tool configuration with the input stream." in the sort tool in Container 44.
Thanks in advance and lesson to learn (save workflow more regularly!!!)
Paul
Hi @messi007
I've set up the configuration like this (XML code easier to show than screen shot) and this seems to work:
<Configuration>
<GroupFields>
<Field field="NewField" />
</GroupFields>
<HeaderField field="RecordID" />
<DataField field="Value" />
<Methods>
<Method method="Concat" />
<Separator>,</Separator>
<FieldSize value="2048" />
</Methods>
</Configuration>
Is this the same as in your workflow?
Thanks,
Paul
Below the config of the cross tab. However try to update and run the workflow.
Please let me know if this solve the problem.
Regards,
@Paul_s_Moody happy to help! :)