Dynamically Select Fields based on Null/Not Null

[laurennewton]

Hi Alteryx community, I think this is probably a relatively simple fix but I can't seem to figure it out on my own!.

Here is my goal: I wany to dynamically select any field that contains "Filter 1" and "Filter 2" in the column header (I figured out how to do this part). But I also want to exclude any use case where a column has data in any fields that contain "Filter 1"  but also has null fields in any column headers that contain "Filter 2".

Ex. In this example I would want to only pull through the Test Group 1 and not Test Group 2

Group Name | Filter 1_Business Unit | Filter 1_Program | Filter 2_Project Code | Filter 2_Plant

TEST GRP 1 |            11221               |      NULL              |       3900                    |     NULL

TEST GRP 2 |            2345                 |        NULL             |       NULL                   |    NULL

[binuacs]

@laurennewton What is the expected result from the above input records?

[laurennewton]

@binuacs In the example I gave I'm looking to only get that first record associated with Group 1 and exclude the Group 2 record because it has null values in all the fields containing "Filter 2" in the header

[binuacs · Accepted solution]

@laurennewton Can you check the below workflow works for you or not?

The dynamic select tool only selects the heading contains 'Filter' (you can update the formula to select only filter1 and 2)

[laurennewton]

@binuacs thank so much! its working perfectly

[laurennewton]

@binuacs a follow up question.. what if i wanted the reverse solution? so i would want to pull only the record where only a column labeled "Filter 1" is returned? I've attached an example file of the structure of the data currently. In this example file I'm looking to return back only the Test Group 2

[binuacs]

@laurennewton I have a question, why the below fields are not in output even though they satisfy the condition

ie Filter_1 is filled but all the Filter_2 are NULL

[laurennewton]

@binuacs i am now looking to achieve the opposite of what I achieved previously. so now i would like to only get back a use case that meets the following criteria:

- any field that contains Filter 1 in the header that is not null

- any field contains Filter 2 in the header that is null.

In the example I attached i have two different test groups data (my real dataset has multiple). Test Group 1 has both a Filter 1 field that is not null and a Filter 2 field that is not null, and Test Group 2 which has a Filter Field that is not null and has ALL Filter 2 fields null. The goal is to now only show Test Group 2.

[binuacs]

@laurennewton I understand your requirement but if you take a look at the field "Filter 1_Pdef", which has data and the rest of the Filter 2 fields are NULL values, which actually meets your original condition where 

- any field that contains Filter 1 in the header that is not null

- any field contains Filter 2 in the header that is null.

Since these fields also meet the above criteria my workflow picks those records as well along with the TEST GR: 2

Do you think I should not consider the field Filter 1_Pdef in this case?

Hope the below pic make sense for you what I was trying to say

[laurennewton]

@binuacs if you look at record number 13 you'll see that Test Group one also has a Filter 2 filled out in the Filter 2_Plant field. The way the database I'm using works is that records are stored separately so if a group has a filter 1 filled out if they have a filter 2 it would be shown separately from the Filter 1 fields.