Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Request for guidance: looping through filter configuration records against a data set

Nicholas_White
8 - Asteroid

Hello,

 

I'm trying to automate a thing we currently do in Excel. The screenshots I'm showing are not of actual data, this is a mockup designed for this post.

I made a video and uploaded it to YouTube if anyone needs to see a proper walkthrough, I'm not confident that my description below is good enough.

 

YouTube video walkthrough 

 

Attached at the bottom is the working file used to make this post.

 

What I'm starting with

In a nutshell, we get an Excel report which lists all the items in our inventory. It looks something like this:

 

vitaminman12_1-1661542066371.png

We have to do various counts of the inventory based on different configurations of the filters. For example, if I want to look count ALL SAX records, I would do something like this in the INSTRUMENT field:

 

vitaminman12_4-1661542351272.png

 

And the result would look like this:

 

vitaminman12_5-1661542381826.png

 

 

I would then do a count of the records, assign it a meaningful Data Point name, and throw it into a list like this (unioned with two other Data Points):

 

vitaminman12_6-1661542438282.png

 

This work is very tedious, I have to do up to 80 different counts. We timed someone doing this a few months ago, we figured that with our actual data (hundreds of thousands of records, about 40 different fields to filter on), it took about 2 full days to get all the points compiled into a list.

 

Through the help of lots of YouTube videos and posts here in the community, I decided to come up with this solution.

 

For each Data Point I'm trying to find, I designed a little template which can be used to describe the various on/off states of each item in each Field filter. Using Yamaha In Stock as an example, I converted this:

vitaminman12_7-1661543044945.png

 

...into this:

vitaminman12_8-1661543224758.png

note that YAMAHA is set to TRUE, IN STOCK is set to TRUE)

 

 

 

I made a bunch of these little filter configurations for each data point I need. Here are 3 examples of Data Points someone might ask me for, and their respective filter configurations:

  1. JAPANESE ALL
  2. SAX ALL
  3. YAMAHA IN STOCK

vitaminman12_9-1661543330262.png

 

Replicating this in Alteryx

So I built a Workflow which looks like this:

 

It can take a filter configurations (2), load it into the filter tools and configure them (big green box), load the monthly inventory report (1) and run it through the configured filters (big green box), and spit out a single record (3)

vitaminman12_13-1661543845881.png

 

 

 

In practice, it looks like this:

 

When I connect the JAPANESE ALL configuration to the and run as Analytic App from the magic wand button, I get the following:

vitaminman12_15-1661544742625.png

 

 

vitaminman12_10-1661543639316.png

 

And when I click Finish, I get the following:

 

vitaminman12_12-1661543779939.png

 

 

This is good, this is what I want.

 

HOWEVER, if I want other Data Points, I have to disconnect the JAPANESE ALL configuration, and reconnect the configuration of the data point I'm interested in, and go through the whole process again.

 

Here it is for SAX ALL:

 

vitaminman12_16-1661544773004.png

 

 

vitaminman12_17-1661544811243.png

 

And for YAMAHA IN STOCK:

 

vitaminman12_18-1661544854253.png

 

vitaminman12_19-1661544882739.png

 

 

That's nice and all, but what I REALLY want is this:

 

vitaminman12_20-1661544989231.png

 

The approach I'm taking is to union all the filter configurations and flatten them to a single record per data point:

 

vitaminman12_21-1661545807567.png

Which in turn makes this:

 

vitaminman12_22-1661545852786.png

 

I would configure the Workflow to go through each of these filter configuration records and apply their filter settings to all of the inventory report data 1 by 1, and the results would get stacked on top of each other at the end until I ran out of filter configuration records.

  

 

I've tried different flavors of Batch Macro, Iterative Macro, Analytic App, regular Workflow, and I can't seem to figure it out. I think my brain is fried from all the tinkering back and forth between Excel and Alteryx, I'm looking for some guidance.

 

My question to the Community:

My question: how do I set my Workflow up to:

 

1. prompt me to load the inventory report

2. the workflow goes through each filter configuration record, configures the filters, and runs the inventory report through it, and spits out a value

3. the resulting values stack on top of each other into a list

4. this loop continues until there are no more filter configuration records left

5. it just runs without any further prompts...not list box interface tools or anything. Simply open the app, load the inventory report data, and wait for the final data point list to come out at the other end.

 

  

Thank you all,

 

Nick

4 REPLIES 4
cpapaioannou
7 - Meteor

I hope this helps :)

You'll see that the WF is silly simple and a lot of improvements/controls can be developed. 

This is because most of the work has already been performed by the team that developed CReW macros :)

Before opening the WF you need to have the CReW macros pack installed. You can easily find it within the community or directly from their website.

 

If your query expressions are so simple, then the operands of IN and NOT IN should work well. Otherwise you could have an "expression builder per query" with a couple of transformation tools.

Nicholas_White
8 - Asteroid

@cpapaioannou ,

 

Wow, that's really slick. Maybe too slick, I don't understand how it works 😁

 

The music store setup was contrived, designed to mimic real data which I can't post online (health insurance). I'm going to try to tinker with my actual data and see if I can get it to work with this tool.

 

Thanks a bunch for showing me this, I'll report my findings when I'm done.

 

Nick

Nicholas_White
8 - Asteroid

@cpapaioannou ,

 

My goodness...it works!

 

I had to do an awful lot of massaging of the source data to get it to fit into the way the macro wants to see the data, and I had to build out a macro to convert the Filter Configuration files into formulas the way the macro wants to see them...but it blasted through 18 of my Filter Configuration files in about 2 seconds over a large data set.

 

Unbelievable. 

 

There are a few hiccups I need to go through:

 

1. where the resulting of a Filter Configuration = 0, it simply ignores it and jumps to the next one. 0 is a valid count for this project, I can't just assume that 'if it doesn't exist, it must equal 0'.

 

2. Some of the Query names have '.' in them. '1.2. Sum of All Records' for example. The Macro doesn't seem to like these and completely ignores those Queries. I did a simple RegEx replace on those so the previous example looks like '1_2_ Sum of All Records' now, and the Macro passes that through just fine.

 

Will post more updates and I get this cleaned up.

 

Thanks,

 

Nick

 

cpapaioannou
7 - Meteor

Perfect!

 

Fair points made. In the attachment both should be addressed now. 

 

Instead of using the Query as an output field, we can use the ID and not mess with Alteryx's renames/underscores. Also we can create a Count field to also get the count-zero queries.

Labels