Filter transactions based on top approved date

Hi Community!

Little help needed:

I have a table with transactions, that have the following columns:
level, company, date, type, value and final.

What I want to do, is filter based on the top yes value on Final based on date, and bring all events on the date and below for that level, company.

What would be the best way to archieve this? Attached an example of the start point and desired outcome.

Thanks for the help! :pray:
example data.xlsx (23.9 KB)

Hello!

Here’s how you can achieve your desired output, this is what the final workflow looks like :slight_smile:


I’ll break down how to achieve it if you (or someone else is in a similar situation)

You first want to filter out all the "No"s and Missing values so we start out with the rule based row filter with the following expression
NOT MISSING $Final$ AND $Final$ LIKE "???" => TRUE

I chose to use LIKE "???" because I noticed a mix of “Yes” and “yes” and this easily avoids me an extra AND clause.

After filtering you want to get the max dates for each company or level by grouping by Level and aggregating by Max Date.


image

You’ll get a table with 3 rows, 1 for each Level and the corresponding Max Date:
image

Once that’s done you can start looping over every row of the aggregated table and filter by that level using the flow variable “Level”, and then excluding the “No” values in another row filter node (you can combine both into a rule based row filter if you want to).
image

Then, you can add a datetime based row filter and select your max date as the end date (Make sure it’s inclusive on the Options tab):
image

You can finally add a sorter node to sort the dates in descending order.
image

Finally, attach this sequence to a “Loop End” node with disabled Iteration Column.
image

And the desired result should be in the output of the Loop End node :slight_smile:

Hope this helps! You can find the workflow attached for your convenience :D, enjoy!

Filter transactions based on top approved date.knwf (85.4 KB)

4 Likes

Thanks a lot @ellysus!! :raised_hands: I’ll try this out :=)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.