Dynamically Filter data (based on the values coming in a column) into separate outputs

Problem Statement:

Create as many output streams (17) as the no. of distinct values (17 in my example) coming in a particular column without using 17 Filters one of each value.

How the output should look like?

Required Output.png

What I have tried so far (with no luck):

I have seen some posts where people have suggested to use the output tool to create separate sheets for each type of values coming in a particular column. Also a macro which essentially does the same thing but iterates as many times as distinct values appearing in the column I want to filter on.

To explain the problem statement I am using Sample Superstore data. It has a column called 'Sub-Category' which has 17 distinct values.

Summarise Screenshot.png --------------> Sub-Category Distinct Values.png

I can create an excel output with 17 sheets one for each sub-category.

Test Output.png

But if I want to build 17 downstream workflows, I'll have to read the 17 inputs again which is as good as putting 17 filters as I have created 2 as a sample:

Sample Filters.png

Alternatively I can create a macro which essentially does the same thing but not exactly what I want.

Packaged Workflow Attached.

Dynamic Filter Workflow.yxzp

Developer Tools

Accepted answers

All comments

caltang

I must comment that your post and question is really well written. The level of detail and attachment, plus clear expectations - kudos @alibink ! As to your question, I would say that the Filter tool is the tool of choice if you're looking to select records of data that are grouped.

But I get where you're coming from. There can be scenarios where new sub-categories are made, and then it no longer becomes "Dynamic" enough to cater. I can suggest for you to pivot your data and make sub-categories your "Field", and use a Dynamic Select tool to get what you want as per your image. That's the high level view... let me try it.

alibink

@caltang Thanks. Precisely. I used the summarise tool to get the updated list of distinct value whenever the flow is run. If a new value is added it will be captured in the output of summarise. I need to find a way now to iterate the list present in the output of summarise tool to get one stream for each of the values. There must be a smarter way of doing it rather than just applying 17 filters, what will we do if these are not 17 but 117 values? 117 filters is certainly not a way to go... and as you precisely mention applying filters won't make it dynamic if the new values start coming.

Looking forward for a solution. Keep me posted on how you go with your try and thanks for helping.

caltang

Something like this:

Now, you can then package it as a batch macro if you'd like, and then combine it with something that has 17 outputs may be a little too difficult for the eye. I would suggest 5 outputs at a time if it helps.

Dynamic Filter.yxzp

caltang

Of course, that solution is something simple, the higher level application is to have an Excel table mapper that is connected via a Control Container to serve as the "Dimnesions" of the select. It'll take some time to do, and I need to rush for a meeting soon. Hope this gives some ideas!

alibink

Thanks @caltang.

In this solution we ended up applying the same number of filters. In your screenshot, you did it for 3 sub-cats with three filters. For 17 we will have to apply 17 filters which takes us back to the point from where we started. Doesn't it. On top of it, we lost track of Sub-Category information as we hardcoded the col as Sub-Category. Also the select tool won't select a new category automatically if that appears. e.g. 'Switches' if starts coming into the data our workflow won't pick that up. Would it?

Or am I missing something?

Appreciate your help anyway. Good luck for the meeting :P

caltang

Actually, now that I review it, yeah you're right. It still has the limitations mentioned, kindly ignore my post @alibink

I'm stumped on this myself now, my thought was to get the count of unique figures and see if Alteryx could iterate it - even though it can, the selection still needs to loop and "tick", which I am not sure how to do myself.

But may I ask - how different are the calculations for each "sub-category" example in your work? Are they very different from each one?

alibink

@caltang That’s alright. There are different set of business rules to be applied to each sub-category so has to follow a different stream within the same workflow. To get things done temporarily I’ve used multiple filters to split my data and applied business logic as required but I’m not quite happy with this approach for the all those reasons we discussed earlier.

caltang

Most of my use cases, I use a Filter tool since my own "sub-categories" usually never exceed 10 in nature. Since yours is #n and also varies, then it's a whole different game.... I'm eager to learn too. I may face this same issue someday.

Have you tried alternatives using Python to do? Perhaps you could package it into a tool as well - just throwing this out here.

alibink

@caltang Yeah its a matter of developing a logic. Once we do that then we can implement it any tool including python. I'll keep working on it to find a solution and post it here if I find something. You do the same

Thanks for your help though.

Yeah packaging the logic in a tool and make it available in the gallery for others to use is good idea. Will do that once we find appropriate and efficient solution.

flying008

Hi, @alibink

Do you want to implement a dynamic filter function even like list box based on unique values for multiple rows within a specified field column?

1- Currently, Alteryx has multiple selected filter that only target multiple columns, not multiple rows even if you implement a row based List box with a macro, it is not updated in real-time unless you run the workflow again to obtain the changed data.
2- Of course, you can use Java script to rebuild the GUI interface of macros, as it can achieve functions similar to keyword search, which will greatly facilitate operability when you have too many items.

alibink

@flying008 I don't mind the pathway of solution as long as it solves the problem.

Do you want to implement a dynamic filter function even like list box based on unique values for multiple rows within a specified field column?

[alibk]: Yes it sounds like it.

[alibk]: I can live with re-running the script to update the values based on changed data. Can you put an example together?

CoG

Out of curiosity, why do you need 17 different output streams (or one for every distinct sub-category)? Will you be running different logic/workflows on every one?

alibink

@CoG Yes each category goes through a different stream with its own set of business rules and logic.

CoG

I still don't really understand why you cannot filter since that is in effect exactly what you are doing in order to run 17 different sets of logic (I guess aesthetic reasons), but here is a dynamic workflow that I think meets your requirements. If you need the category column, you'd just need to join it to the sub-category before running through the macro setup.

Main Workflow:

Packing Macro:

Pack Macro.png

Unpacking Macro:

Unpack Macro.png

Dynamic Filter.yxzp

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235