IF conditions with Filter
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
