Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Filter Tool - Return with Filtered criteria in new column

Highlighted
7 - Meteor

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

 

Highlighted
Alteryx
Alteryx

Hey @Ahmad_S 

 

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

 

Capture.PNG

 

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

Highlighted
Alteryx
Alteryx

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!

 

bpatel_0-1574783394703.png

Highlighted
7 - Meteor

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
7 - Meteor

Hi Everyone,

 

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

 

Ahmad_S_0-1574797000150.png

 

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.

Highlighted
Alteryx
Alteryx

@Ahmad_S ,

 

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

 

with only the formula tool:

bpatel_0-1574800319852.png

or 2: with the filter first and then formula tool

 

bpatel_1-1574800423275.png

 

on the right track? hope this helps

 

 

Highlighted
7 - Meteor

Hi @bpatel 

 

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

 

Can you copy paste here for quick reference?

 

Regards

Alteryx
Alteryx

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

Highlighted
7 - Meteor

Thanks a lot @bpatel - it worked!

Labels