Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

Transpose indefinite row data into columns

6 - Meteoroid

Hi Alteryx Community, 


I'm having trouble with using the cross tab to create a trend analysis. The first column contains the names and the second column contains all the dates and values. To put it visually, see below. Take note that it's just the sample


Name      | Value           - Header Row
Field        |  1/2/2019     - 1st row

Jay          |  1000

Spence   |  123

Reese     |  478

Field       |  1/3/2019

Jay         |  1100

Spence   | 145

Reese     | 528

Ruth        | 900

Field       | 1/4/2019

Ruth       | 850

Spence  | 500


As you can note, the data is indefinite, and there's no work around there since the source file is being maintained by someone else. So, I think the next steps are to create a new column with the dates only per row, so that I can use the cross tab to group it. I have no idea on how to create that new column with just the dates indicated per row. 


Please let me know if this makes sense or if I need to explain further. Would appreciate any help on this. 


Hi @Loading___ 


You can use a Multi-Row Formula tool and an IF statement to create this new column


IF [name]="Field" THEN [Value] ELSE [Row-1:Value] ENDIF


This will create a new column and only add in the value if it has the date field associated with it - all of these have the first column as 'Field'. Then for all of the blank rows it will then go and reference the date from the field above.


Let me know if that works for you


Kind Regards


6 - Meteoroid

Hi Davis, 


Really appreciate the quick response on this. The solution worked for only the initial 2 rows but I changed the ELSE statement to [Row-1:New Field] and it worked. Thanks so much for your help!