Transpose indefinite row data into columns

[Loading___]

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. 

[wdavis · Accepted solution]

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

Will

[Loading___]

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!