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

[alibink]

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?

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. 

-------------->

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

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:

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

Packaged Workflow Attached.

[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.

[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]

Im on my phone rn, but the main difference for my filter is that it's the same name if you noticed. So you don't have to edit the filter anymore, the dynamic rename gets new names and just changes it accordingly.

Because u wanted 17 "Selects", I did it to match yours to give you the idea of how it would look like, otherwise if you want to do consistent formulas on the sub-categories, you can use a Batch macro like you showed to do what you want.

Re your point on Select, if you select "Dyanmic or unknown" then it will pick up new fields that come in

[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.