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

[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

[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.

[bpatel]

@Ahmad_S ,

i tried two different ways.. hope this is what you are looking for.

with only the formula tool:

or 2: with the filter first and then formula tool

on the right track? hope this helps

[Ahmad_S]

Hi @bpatel 

I am unable to see complete formula for next "Contain".

Can you copy paste here for quick reference?

Regards

[bpatel · Accepted solution]

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

[Ahmad_S]

Thanks a lot @bpatel - it worked!