Alteryx Designer Desktop Discussions

Paul_s_Moody · ‎07-10-2023

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.

alexnajm · ‎07-10-2023

Transpose then CrossTab should make this work!

messi007 · ‎07-10-2023

@Paul_s_Moody,

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

Paul_s_Moody · ‎07-11-2023

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

Paul_s_Moody · ‎07-11-2023

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

Post 88 Post 89 GMP

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

Record ID tool
Formula tool - replacing the data in the blank column using record ID with the original column titles:
Select tool to drop Record ID

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

Post 88 Post 89 GMP

incIndexTable

Fixed_0

CPI_0_3

Fixed_4

RPI_0_5

Fixed_4

Thanks for both of your help on this.

Paul_s_Moody · ‎07-11-2023

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

Paul_s_Moody · ‎07-11-2023

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

messi007 · ‎07-11-2023

@Paul_s_Moody,

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 · ‎07-11-2023

Hi @messi007,

This works perfectly!

Thanks again.

messi007 · ‎07-11-2023

@Paul_s_Moody happy to help! :)

Alteryx Designer Desktop Discussions

Replicate Excel Paste¦Transpose in a workflow