6 Replies Latest reply on Jun 26, 2018 8:44 AM by Jim Dehner

# Filtering by OrderNo based on results from calculation

Hello!

I am trying to filter out all Order Numbers that have the term 'AP&C' in item description.  These Order Numbers are not unique (sample table below for illustration), but I want to remove all orders associated with the order numbers that have that term (remove 1, 2, 4). For solving this, I created a calculated field that gives the order no.s and 0's. I wanted to use this as a filter (using orderno) in my worksheet. The issue that I am facing is that I am not able to remove these OrderNo.'s as by applying all the other filters that are relevant to the calculation, the calculated field becomes useless as the source data for both the worksheets becomes disjoint. It would work if I were to export the OrderNo.'s and then import that sheet to filter out unwanted OrderNo.s. Is there any other way this could be done automatically (without the export-import step)?

 OrderNo Item Description 1 item AP&C 2 item 2 item AP&C 2 item 3 item 4 item 4 item AP&C 4 item 4 item 4 item

• ###### 1. Re: Filtering by OrderNo based on results from calculation

Try This:

If Contains(Item Description, "AP&C") THEN 0 ELSE ORDER No END

Using 0 you can easily Filter out

Thanks

Deepak

• ###### 2. Re: Filtering by OrderNo based on results from calculation

Hi Deepak,

Thank you for your response. But it is not going to work out in my case. I have added a sample workbook to explain the issue more clearly. I want to remove 34534 to 34546 from the first worksheet which currently is including all orders since OrderNo.'s are not unique.

• ###### 3. Re: Filtering by OrderNo based on results from calculation

Here It is:

You Need to Use This Calc top Filter and Set TRUE

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 4. Re: Filtering by OrderNo based on results from calculation

Thank you. It does solve the problem in the sample that I provided but it doesn't seem to work in the real data set.

• ###### 5. Re: Filtering by OrderNo based on results from calculation

This is the custom SQL query workaround I have used.

SELECT * FROM [dbo].[FMD15_18]

WHERE [OrderNo] NOT IN (SELECT [OrderNo]

FROM [dbo].[FMD15_18] WHERE [Item Description] Like '%AP&C%')

But it would be more helpful if there was a way to do this in calculated fields. If anybody knows how to do this, please let me know.

• ###### 6. Re: Filtering by OrderNo based on results from calculation

Hi

not sure I fully understand your goal but is this the output you expect for Orders that do not contain AP&C

if so the formulas are

and

set on filter shelf and set to true

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful