Split/filter data dynamically for multiple outputs

[stefaniadurdan]

Hello,

I have a situation which requires a dynamic solution in case input data structure is changed.

Briefly, this is the input file with products in different stores and each store being located in a city (same product can be in multiple stores, but of course same store cannot be in multiple cities).

What I need is to use a Cross Tab tool to have the stores as columns and make the sum of the price per product. But I need this for each single city separately - in this case, I only have 2 cities (C1 and C2) and I could use a filter before the Cross Tab and this way to split the data per city. However, I need a dynamic solution which implies that if the input file changes and I will have 5 or 50 cities to also have 5 or 50 output files (multiple excel sheets or multiple sheets in same excel file, doesn't matter). 

I am not sure if and how I could obtain that in Alteryx. If you have any solution, you would really save me :)

This is how the output should look like:

What is really important is not to have stores which would not be a part of the city and with 0/null value - this would happen if I just used a Cross Tab with no other filter, but I do not want to see stores which are not relevant and of course with no data.

Attached also the Excel file with the input and output expected.

Thank you!

[FrederikE]

Hey @stefaniadurdan,

You could use a batch macro to create separate sheets (per store) and filter to the Cities and then remove empty columns before you write the file. 

See attached Workflow(s). 

This would be the output:

[stefaniadurdan]

Unfortunately I cannot open the workflow. I am using Alteryx Designer 2020.4 and it notifies me the workflow you uploaded is done with a newer version.

Would you be able to change it so I can use it? Thanks!

[Christina_H · Accepted solution]

This is actually really easy if you put the crosstab inside the macro.

@stefaniadurdan In case you can't open the attached, it's simple to set up.

Main workflow:

Macro:

[stefaniadurdan]

Hello! I tried to do it as per your instructions but I still got stuck. I do not find any macro with 2 input anchors as you have.

Would you mind checking the settings I built and see what I did wrong?

Thanks!

[Christina_H]

Hi @stefaniadurdan 

The step you've missed is to convert the macro from a standard macro to a batch macro, found under the workflow configuration

If you convert to a batch macro and save, the new anchor appears for the control parameter.  Link your summarize tool to that, and the main data to the regular input anchor.

[stefaniadurdan]

Hello, @Christina_H .

Thanks for the details - now it works, but partially.

When I run it, the output is showing me the data only for the last city, even though Alteryx results show there were 2 iterations (having 2 cities).

I grouped them by City in the Macro:

And this is the summary configuration:

Do you know how I can solve that?

Many thanks!

[Christina_H]

Check the configuration of the output tool in the macro.  You need to check the box at the bottom to take the name from a field, and set it to name the sheets per city (see macro screenshot on my original reply)

[stefaniadurdan]

Thank you, @Christina_H . I have accepted your proposal as solution, it works as expected. Thank you so much!