Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Filter Tool - Return with Filtered criteria in new column

Ahmad_S
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

 

9 REPLIES 9
LukeG
Alteryx Alumni (Retired)

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

bpatel
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

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

RolandSchubert
16 - Nebula
16 - Nebula

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

bpatel
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

 

 

Ahmad_S
7 - Meteor

Hi @bpatel 

 

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

 

Can you copy paste here for quick reference?

 

Regards

bpatel
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

Ahmad_S
7 - Meteor

Thanks a lot @bpatel - it worked!

Labels