Filling up each cell in a table by matching row values with column headers

[wuaw]

Hi team,

I have a scenario where I need to create some form of trend analysis. I have done some data massage and have reached the part where I have columns arranged in ascending order (dates) and thousands of rows of dates (converted to string). Both rows and columns are in the same date range. I need to match each row with the corresponding header and populate that cell with value "1"

I wish to get your expertise whether the multi-row formula tool is able to achieve this task. A sample workflow would be greatly appreciated.

Attached are two sample files:

Date_underscore.xlsx

Date_underscore (desired output).xlsx

Thank you

[Qiu · Accepted solution]

@wuaw 
Cross Tab and Transpose Tool should do the trick. And we need record to keep things orgnized.

[lmorrell · Accepted solution]

Hi @wuaw 

Packaged workflow is attached.

This can be done by identifying each row within your dataset, Transposing the data, applying an IF statement to change the value of a cell if the row value is equal to the column name, and then restoring the original data structure with a Crosstab.

Hope this helps! 

[TheOC · Accepted solution]

Hi Wuaw, I believe i have managed to solve this using a single Multi-Field Formula tool, feel free to checkout my solution below!

I used the [_CurrentFieldName_] to check the value of the [Date_String] for each row to see if it matched the name of the column, and in that case, output 1. In the case that this wasn't matching, just outputted null.

Hope this helps, a workflow is attached!

[Qiu]

@wuaw 
Thank you for the mark.😁

[wuaw]

Hi Qiu and lmorrell,

Thank you for the quick response! The solution is perfect!

Have a great day ahead

[Qiu]

@TheOC 
This is briliant. Learned something about Multi Field Tool. Thanks.

[TheOC]

Me too! I'd say its my favourite tool, as it looks intimidating but you can do so much with it. Any chance to use it I'll take... although you guys timely beat me to a solution, well done!

[Qiu]

intimidating indeed. But I see its charm.
The answering process here is now really competetion of time. I guess Alteryx is very happy about it.

[wuaw]

Hi TheOC,

This is brilliant. It worked pefectly.

Thank you for sharing your knowledge!