IF conditions with Filter

[tom05]

I am looking to perform a filter based on multiple conditions.

If doc number contains 380 and 225 then return the line containing 380

If only 225 in doc type then return the line.

Example data source attached.

If anyone can suggest a custom formula to use within the filter tool or if another tool is more appropriate?

Thank you in advance.

[apathetichell]

Summarize tool:

Group by doc number id column

Max your column with your numbers

That will return the higher number in the document group (ie 380 if its there or 325/225 if 380 isn't there.).

filter is great at separating records in the datastream but it doesn't change values inside the records.

Attached is the solution with summarize.

[Emil_Kos · Accepted solution]

Hi @tom05,

I have prepared a workflow for you:

The output:

[Kenda · Accepted solution]

Hi @tom05 

I solved this using a Multi-Row Formula tool. First I sorted the data by Reference - Ascending then Doc Type - Descending. Then I used the following expression in my Multi-Row Formula tool, grouping by Reference:

iif([Doc Type]=380 && [Row+1:Doc Type]=325, "Yes", iif([Doc Type]=325 && [Row-1:Doc Type]!=380, "Yes", "No"))

Hope this helps!

[Maskell_Rascal · Accepted solution]

Hi @tom05 

Depending on how the rest of your data looks, I have two different solutions for you. 

The first one leverages the Sample Tool after sorting the data by Reference(Ascending) and Doc Type(Descending). 

The second method creates groups based on Reference after sorting, and then a Filter tool selects the first record of every group.

Attached is the workflow. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks,

Phil