We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
Weekly Challenge
Solve the challenge, share your solution and summit the ranks of our Community!Also available in | Français | Português | Español | 日本語
IDEAS WANTED
We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!
Submit Feedback
Nice Exercise, I can't wait to learn the simple approach.
Wanted to create a workflow that wouldn't require manual selecting and renaming with a Select tool (see @JoeM, I do use other tools).
Spoiler
First steps were to standardize the data, so that one row is the question, and the next row provides the answer. This involved 2 steps. 1) remove the rows that are just Q1, Q2, etc., and 2) standardize the rows with the question and answer blended together.
For 2, I separated out just those rows (qualified by "H" in the formula tool early on in my workflow) and stacked them on top of each other with a Union.
I then used a Multi-Field formula tool to fix all of the columns (remove the "What is your", use title case, separate the questions in the rows cited as 2) above into just the question and just the answer. This was a bit of a convoluted IF THEN ELSEIF, but it worked well, and one tool only!
One more dynamic select to choose just the F1, F2, etc. columns. Then the same process as many others. Assign a record ID, transpose the data, crosstab it into columns.
Separated the questions from the answers with dynamic select, Unioned back together, and renamed with Dynamic rename.
And there you have it. A way to dynamically choose columns, rename columns, etc., without manual work in a Select tool.
For 2, I separated out just those rows (qualified by "H" in the formula tool early on in my workflow) and stacked them on top of each other with a Union.
I then used a Multi-Field formula tool to fix all of the columns (remove the "What is your", use title case, separate the questions in the rows cited as 2) above into just the question and just the answer. This was a bit of a convoluted IF THEN ELSEIF, but it worked well, and one tool only!
One more dynamic select to choose just the F1, F2, etc. columns. Then the same process as many others. Assign a record ID, transpose the data, crosstab it into columns.
Separated the questions from the answers with dynamic select, Unioned back together, and renamed with Dynamic rename.
And there you have it. A way to dynamically choose columns, rename columns, etc., without manual work in a Select tool.
