Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hi,
I have a set of data in a format like the below screenshot.
Essentially I need the Region columns transposed to align with the rules. This is done by checking whether the field = TRUE. Any Regions with FALSE or [Null] should be ignored and I do not want to see them in the final data set. I am trying to create a list of rules, along with the regions that they apply to. This will ultimately be used as a reference table.
Equally if a rule has no regions = TRUE, then that rule needs to preferably have a "None" assigned to it, or otherwise be dropped from the final data (Rule 2 is an example of this).
Is this possible?
Solved! Go to Solution.
Here is the sample workflow for your case. Hope this is helpful.
I saw this one and wanted to see if I could do it in a single data line. So here's another option!
The Multi-Row Formula tool is just my sneaky way of keeping Rules where none of the Regions are TRUE --- in your example, Rule2. The new field will only flag TRUE if the row directly above it is Null and the row directly below it is Null. This will only happen if it's the last record for a given Rule, and none of the records for the entire Rule had a Value of TRUE.
Color coded, hopefully making this easier to follow:
IF [Value] THEN 1=0 ----- If the Value on the current row is TRUE, set the new field to FALSE. At this point, we know that the given Rule is going to make it through our Filter tool, so there's no need to keep any "extra" records.
ELSEIF IsNull([Row+1:Rule]) AND IsNull([Row-1:Filter]) THEN 1=1 ----- This will only be set to TRUE if it's the last record for a Rule (IsNull([Row+1:Rule])) and if the Value that has been copied down is Null (IsNull([Row-1:Filter])), which will only happen if none of the previous rows were FALSE.
ELSE [Row-1:Filter] ENDIF ----- If none of the other conditions in this IF statement are TRUE, just grab the value that was calculated for the row above (TRUE, FALSE, or NULL). This will allow me to perpetuate any FALSE's I find in the list for a given Rule.
Look at your example. Note that this is grouped by Rule, so every time we hit a new Rule, the row just above that Rule is actually Null.
Record#
1- Value is TRUE, so the new field (Filter) is set to FALSE.
2- Value is not TRUE and Filter field for Record#1 is not NULL, so this field takes the Filter field from Record#1, FALSE.
3,4,5- Same logic as Record#2. FALSE.
----- Grouped by Rule, so there's a "break" in the data here because it's a new Rule and it's as if we're starting again with the first record -----
6- Value is not TRUE, and Rule for Record#7 is not Null, so this field takes the Filter field from the Record above, which because we've grouped by Rule is NULL. There is no spoon --- I mean record --- above this one.
7- Value is not TRUE, and Rule for Record#8 is not Null, so this field takes the Filter field from Record#6, NULL.
8,9 - Same logic as Record#7.
10 - Value is not TRUE, the Filter field from Record#9 is NULL, and because this is the last row for Rule2, the Rule field for the record below is also NULL. This is the unique case where every single record for a Rule had a Value of FALSE, so we need to pull a record through to make sure the Rule doesn't get entirely Filtered out. This row is set to TRUE.
I'm not sure if this explains or just muddies the water more. As I've written it, I've realized that all of the TRUEs and FALSEs make it kind of difficult to keep everything straight. Let me know if you need more confusing explanations!
