Filter Tool - Return with Filtered criteria in new column

Ahmad_S

Hi Alteryx Gurus,

I have small query and unfortunately due to confidential data, I can't post example here.

My data-set is like this:

Column A | Column B | Column C

1 | ABC | Maximum

2 | DEF | NIGHT

3 | LFG | Stupid

So I am input the file and then filtering using "CONTAINS([Column C], "NIGHT")" and doing it for multiple fields. I want to have another Column D showing that this field was filter based on "NIGH" criteria.

Appreciate if you can help or guide me in this. I have already tried to find few examples but unfortunately they were not that clear.

Regards

Transformation

Accepted answers

bpatel

my apologies @Ahmad_S .

here you go!

if Contains([column c], "night") then "night n34" elseif Contains([column c], "n34") then "night n34" else null() endif

All comments

LukeG

Hey @Ahmad_S

Here is a workflow I put together that should do what you are looking for (if I understood correctly). Workflow Attached

This applies a filter using the contains function, and then adds a new column (D) that shows what the filter criteria was.

Let me know if you have any questions or if there is anything else I can help with!

Luke

NIGHT Filter.yxmd

bpatel

hi @Ahmad_S ,

is this what you are looking for? i filtered on "night" and added a column that identifies if the record was filtered on "nigh" by adding 1 if yes and 0 if no..

hope this helps!

Ahmad_S

@bpatel - this is something close to what I want but rather than 0 or 1, I want it to show me filter criteria like "Night". Further, I have multiple CONTAINS (almost 100+) so typing for each would be difficult.

RolandSchubert

Hi @Ahmad_S ,

the Filter tool has no option to mark filtered records using a comment. I would suggest to use either a Formula tool first
Formula calculating Column 😧 IIF(CONTAINS([Column C], 'Night'), 'Filtered based on "Night" in Column C', Null())

and then a Filter tool (!IsNull([COLUMN D])

If the content of the column matches exactly, another option is to use a list of items to filter (one column containing "NIGHT",
second column 'Filtered based on "Night" in Column C') and to "filter" by a Join tool ("J" anchor will return only the items fulfilling
the condition and the comment).

Could this be an approach?

Best regards

Roland

Ahmad_S

Hi Everyone,

I tried IIF solution but instead of returning the text which I want, it is returning this:

Also even "Contain" is not working properly.

Example of criteria used:

IIF (CONTAINS([Change Description],"Temenos"), "Temenos T24", Null())||
IIF (CONTAINS([Change Description],"T24"), "Temenos T24", Null())

I have multiple filter criteria like these. Appreciate if you can help in this.

NOTE - If I try just one IF criteria based on @bpatel suggestion then I can get string value but if I do multiple IF with OR and AND then it just shows boolean.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703