This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Total noob to Alteryx, this is my first workflow. I am looking for a way to fill fields to the right of the current field that is based on the current field.
In the attached sample data, you'll notice that the column headers are a combination of blanks (beginning with F) and Categories (A through H). Starting with Category A, moving to the right, I want fields 'Category A' through 'F21' to be filled with 'Category A'. Similarly, I want 'Category B' through 'F41' to be filled with 'Category B', etc.
I imagine I would have to do some kind of transpose, then some kind of fill command, then a cross tab tool to get it back into column/field form.
Additionally, after the fill has been applied, it would be great if ascending numbers could be assigned to the newly filled fields. i.e. CategoryA1, Category A2... Category B1, Category B2, etc. See the attached file titled 'FieldNames' for a complete list.
I realize that what I'm trying to accomplish can be done with a text input file, however I need the solution to be dynamic in the sense that in other examples of the input data, there may be more categories, and there may be more instances of each category. For example, some only go to 19 in this data set. However, other input data sets may go to 29, 30, 31, etc. They represent days in the month.
Thank you very much for taking time to help resolve this!
I intentionally set the different field headers to "Category A," "Category B," etc. to protect the client's confidential information, however I'm now figuring out that by using this naming sequence, it doesn't quite solve the issue I have.
Let's say each field header is a different word. Let's use some new made up headers like "Received," or "Declined," for example.
On the first Formula tool in your workflow, would it make sense to modify the formula to something like this?
IF StartsWith([Name], "Received") THEN "Received " + Right([Name], 1) ELSEIF StartsWith([Name], "Declined")
THEN "Declined " + Right([Name], 1)
Similarly, on the second Formula tool, would I use something like this?
IF Left([Name], 1) = "F" THEN "Received " + [Column letter] ELSEIF Left([Name], 1) = "F" THEN "Declined " + [Column letter]
ELSE [Name] ENDIF
I've personally tried modifying the Formula tools myself using the methodology above, but it doesn't quite result in the output I was looking for. You've definitely gotten me a lot further than I was before though! Thank you for that.
Thank you very much for taking time to help resolve my issue!
I was unable to open your workflow because it is a package, and it is built in what appears to be the latest version of Designer. The version of Alteryx I have available to me is 2019.3.1.24384. I'm not sure if you're able to save your workflow to that version. Your help is appreciated though! Perhaps you can try resaving it?
After a bit of tweaking, I was able to come up with a solution that can apply to multiple field names!
I modified the first Formula tool to read something like this:
IF StartsWith([Name], "Received") THEN "Received" ELSEIF StartsWith([Name], "Declined")
ELSE [Name] ENDIF
On the Multi-Row Formula tool, I used the following (set to Update Existing field):
IF StartsWith([Name], "F") THEN [Row-1:Name] ELSE [Name] ENDIF
The difference here is rather than creating a column for the letter, i.e. A, B, I took that out and used the name contained within the column itself. So rather than using [Column letter] I used [Name].
The Tile tool and the second Formula tool remained the same.
I will give you credit for the resolution! Again thank you so much for your help! You've saved me hours of head scratching.