Filtering Multiple Columns

bogdansheremeta

Below is my sample of data:

[Data] [Sacramento?] Delinquent

Ha No No

Da No No

No No No

Sacramento Yes No

Ha No No

Delinquent No Yes

I need to proceed with my workflow only if there is a Yes on Sacramento and a Yes on Delinquent. So a Filter tool. However, I cant use IF AND as it will not work. Any suggestions on how to make this work?

Workflow

Developer Tools

Accepted answers

flying008

Hi, @bogdansheremeta

Maybe this is your want result ? (In fact, you want get [Sacramento] = 'Yes' and [Delinquent] = 'Yes' , I use [Sacramento] = 'Yes' or [Delinquent] = 'Yes' to explain the filter logic. Note that it is dynamically filtered when row value = field name.)

录制_2022_06_30_08_33_37_439.gif

Input			Output
Data	Sacramento	Delinquent	Data	Sacramento	Delinquent	Filter
Ha	No	No	Ha	No	No	0
Da	No	No	Da	No	No	0
No	No	No	No	No	No	0
Sacramento	Yes	No	Sacramento	Yes	No	-1
Ha	No	No	Ha	No	No	0
Delinquent	No	Yes	Delinquent	No	Yes	-1

All comments

DataNath

If you need to filter out anything that isn't a yes for both Sacramento and Delinquent then that's definitely the way to go. In your sample data none of the records satisfy that condition and so would all be filtered out/come out of the false (bottom anchor) of your Filter tool. I added an additional row that would hit this criteria and the Filter works as per your requirement, unless I'm missing something? Also, I notice you mention 'IF' in your original post. When writing expressions in the Filter tool, you don't write it as a full if statement, just the actual condition. The tool has True and False output anchors and so you just need to put in a boolean expression that will check whether or not something is True/False and output data will via the corresponding anchor, i.e. [Field 1] > [Field 2], or in your case, [Sacramento] = 'Yes' AND [Delinquent] = 'Yes'.

bogdansheremeta

How would you do it without an additional row? I have many other records of data afterwards and needs to be dynamic in checking these two columns

DataNath

@bogdansheremeta the Filter tool will always just check those two columns as that's what is reference in the expression. I just added a new row to show you that it works, as none of the rows in your sample data would satisfy your criteria. If you place a filter into your own workflow with actual data and use the expression I did (below), does that give you what you expect?

[Sacramento] = 'Yes'
AND
[Delinquent] = 'Yes'

bogdansheremeta

No, because it looks for both of these conditions, "Yes" for Sacramento AND "Yes" for Delinquent. So for True there is nothing. Remove your "Do" row and you will see what I mean.

DataNath

In your original post you said 'I need to proceed with my workflow only if there is a Yes on Sacramento AND a Yes on Delinquent.'

So this would be the expression I posted above. If there are no true outputs and you're expecting there to be, are you sure you don't mean:

I need to proceed with my workflow only if there is a Yes on Sacramento OR a Yes on Delinquent.?

If so then you would use:

[Sacramento] = 'Yes'
OR
[Delinquent] = 'Yes'

If not then we'll obviously need to figure out the criteria you actually want to apply.

bogdansheremeta

That is correct. I "'need to proceed with my workflow only if there is a Yes on Sacramento AND a Yes on Delinquent." The problem is that it is Yes on row 4 for Sacramento and Yes on row 6 for Delinquent. I guess a way I can solve this is if I add another row and make them both yes. How do I do that within Alteryx and not manually?

DataNath

You could split the data into two streams and do the check separately, so you don't lose rows where the expression doesn't apply to both (i.e. all of them in your sample). However, I'm not sure how you plan/want to join them back together afterwards. In your example of rows 4 and 6 above, are they fine to be joined back together purely on position because they're both 'yes'? As there's obviously no other unique identifier to join them on, especially when you expand this to more rows in your full data set. Workbook attached:

Filter two columns.yxmd

flying008

Hi, @bogdansheremeta

录制_2022_06_30_08_33_37_439.gif

Input			Output
Data	Sacramento	Delinquent	Data	Sacramento	Delinquent	Filter
Ha	No	No	Ha	No	No	0
Da	No	No	Da	No	No	0
No	No	No	No	No	No	0
Sacramento	Yes	No	Sacramento	Yes	No	-1
Ha	No	No	Ha	No	No	0
Delinquent	No	Yes	Delinquent	No	Yes	-1

grazitti_sapna

@bogdansheremeta, does this solves your purpose?

Thanks!

bogdansheremeta_workflow.yxmd

bogdansheremeta

This is great. can you post a full workflow for this? @flying008

DataNath

I don't see the difference between that and using an OR statement in a filter, as I suggested. Unless I'm missing something?

flying008

Hi,@DataNath

Please look my post output table(the yellow and blue cells), I assume the position is Yes when he needs the value of the [Data] field column to correspond to the names of the following field columns, Obviously you can't do this with just the filter tool.

flying008

Hi, @bogdansheremeta

Dear, I can't post wf because SP. So you can implement it following the idea of the example flow gif.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703