Filter Contains

[suby]

Sorry but I have created a new thread to avoid confusion and I have attached the excel sheet with the desired output.

please help me to get the desired output based on the same data.

Thanks

[grossal · Accepted solution]

Hi @suby,

what's the Join rule? I just can't figure it out. Why is it Apple twice and Mango once? and why are all the others ignored?

Why can easily get to a result like this:

But I am not sure what's the rule to get yours:

I'll attach what I did. Take a look and tell us what's the exact Join rule.

Best

Alex

[suby]

Thanks for sharing but I cant download the workflow. Can you please resend the workflow.

In my case.

Steps I followed.

1) Used the Input Tool to bring the Two tables

2)Used the Join tool to Join by ID on Table A & Table B

Then I want the Desired output as per my excel but when I tried to put the Filter tool  with the condition below

contains([FRUITS],"Apple")
OR contains([FRUITS],"PINE APPLE")
OR contains([FRUITS],"MANGO")

the output is picking up first value from the Table B something like this

1 Vegetable ORANGE

2 Vegetable MANGO

But instead what I  wanted is

1 Vegetable APPLE

2 Vegetable APPLE

3 Vegetable MANGO

[grossal]

I have updated me workflow to use your Filter.

Output:

It will also produce Mango for ID-2, not sure what's if you missed that in your example or if the ruling has to be adapted. I also added a Unique-Tool to remove duplicates.

Workflow attached.

Best

Alex

[suby]

Thanks for sharing.

I've updated my workflow and produced the out put screen shot but I want the output for iD 2 to pick up Apple rather than Mango.

Thanks

[grossal]

What's the rule behind it?

We might get into similar situations with other combinations. There needs to be rule what has the priority.

The best would be if you add a priority column to your fruit input. Something like this:

There are many possible combinations, so it would be best if all priorities inside one ID are unique. 

Best

Alex

[suby]

Hi

Thanks but do know what you mean by Rule.

Apologize and appreciate your time and effort. Could you please advise how to use both contains and Not contains when we filter the records.

[suby]

Thanks Grossal please ignore all my previous messages.

See my screenshot of my tables in the attached .PNG file

Let me explain this clearly and I have Table A and Table B and an expected output Table.

My requirement is

- Firstly join the tables using the Join Tool using the ID column.

- Secondly  I created a New column using the formula tool  called   "Code starts with I"

Now I need the expected output Table but when I added the formula to the new column I am getting a different output from Alteryx.

Grassol hope this makes clear now .

[grossal]

This should do the job:

Left([Code],1) = 'I'

Left will extract characters beginning from the very left and 1 gives you only the starting character. 

[suby]

Thanks your reply and is there way to match the extract string in my existing  formula please?