How to use 'Cross tab' transpose 1 column to table in 1 step ?

[ups366]

Dear all ,hello !

please see the below picture, how can do it in 1 step ?

thank you very much !

[MarqueeCrew]

First you'll need a multi-row formula tool to create your second column. You'd search for something like:

tonumber([field]) > 0

if that's true then the new field equals the field value. Otherwise take the new field value from the previous rows new field. 

this will help. 

next filter out and field equals new field. 

now the cross tab is ready. 

cheers,

 mark

[Nezrin]

By 1 step - do you mean by using only the cross tab tool and nothing else ?

not sure if you can do that because you need another field to represent the column headers - by using a record id tool for example and then use cross tab to transpose it 

[ups366]

Dear all , hello !

@MarqueeCrew 

Could you please show the workflow or sample.yxmd to explained your steps?

Because i can flow you , but the result like '000001,000002,000003    a1,b1,c1' in one field.  Thanks a lot !

@Nezrin 

Thank you !

I got the expect output use transpose function in excel is so easy. so want to realization it use least steps in alteryx. 

[OliverW · Accepted solution]

Hi @ups366 !

Cross Tabbing a table that has just one column to start with will always require at least 2 tools in Alteryx this is due to the Cross Tab tool needing a Name column (to create the headers for your new columns from) and a value column (that identifies with the name column together which data should go into which cell). I am not sure how you actually would do this in Excel with just Transposing, when I tried this, I got this:

Depending on your real data look, you will need one (or more) formulas to identify, which cell of your original data should go in which column / row in the transformed data.

In your case, you will actually need to do the following:

1) Identify how many columns you will need for the data after the transformation and also what the names of these columns should be (can be done most likely with one step in one Multi-Row Fomula tool, depending on the look of your data)

2) Identify which cells should go into which data row after the transformation

3) Transform the data.

I have created you an example here, this uses a Length Check formula (looking for length of 6 characters in your Str column)  to identify which cell should go into which data row after the transformation (similiar to MarqueeCrew's tonumber, just a different check, it will actually depend on your real data which check willwork). Then I am using a second Multi Row Formula tool to create a second new column that helps to identify dynamically how many columns will be needed in total and also how they shall be named (just numbers from 1 to 6 in this case) and then actually the Cross Tab tool to transform the data, hope this helps.

Best wishes

Olli

[ups366]

HI,@OliverW 

Just so so ! Thank you very much !

@Nezrin @MarqueeCrew 

Thank you both for explanation.