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

Dynamically Filter by Source & Export to Multiple Excel Files

jsheehan010
6 - Meteoroid

Hi all, 

 

I was hoping that you could help me with a very simple issue that I cannot seem to get my head around. Any help would be greatly appreciated. 

 

Background:

Step 1.

Take one excel file with one tab (Primary), filter that by one field in column A & then post that data to another specific field in a separate excel file with multiple tabs (Template).

Step 2.

After the info from Primary is transferred to the specific tab in Template, I need to take a different tab in Template (that is calculated based on the original import tab into "Template") to an export file. 

 

The complication I am running into is that I need this to be able to run for multiple filters in column A of Primary and produce multiple Templates files as an output. The user of the app also needs to be able to select the filter in column A of Primary by using a List Box tool. 

 

Ideally, there would be around 30 or so Templates that need to be created. My hints were to use macro & use the block until done tool. I just can't seem to logically visualize how to do this in Alteryx. Again, any help would be greatly appreciated. 

 

Thanks,

 

12 REPLIES 12
DavidP
17 - Castor
17 - Castor

I think what you want to do is perfectly doable. If you can get it working for one filter, converting it to an app where you can run it multiple times with different filters is pretty easy.

 

 

Have a look at the attached. I think it does what you want for one filter. I'll post separately with the app attached.

 

iput output file.png

 

DavidP
17 - Castor
17 - Castor

Here is the app... with a few minor improvements.

 

I'm assuming the reason you want to do it this way is because it will be too difficult to reproduce the logic from your template file in Alteryx? 

 

If you could do that and remove the reliance on your template file, it would probably be a cleaner solution.

 

iput output file app.png

jsheehan010
6 - Meteoroid

Hello again, 

 

David, thank you for your help! I really appreciate it! I tried to follow your logic & I was getting a little tripped up. As I am a new Alteryx user, you'll have to bear with my inexperience! Maybe if I attach the two files Im using, it may make more sense to you. Which in turn, will hopefully make more sense to me! 

 

Please see below for reference to the two files I'm using. 

 

To provide further context, I am trying to use the "Primary" data to input into the "Template", for multiple "Trial's" (located in "Primary") using the same "Template." The data from the "Primary" would be selected based on the "Trial." (about 30 of the 70 listed) Once the "Trials" are selected, those rows of data will be put into a seperate "Template." Specifically within the "Manual Examples" tab; cells B7-B23. The correlation from "Primary" to "Template" is based on "Examples" 1-17 in "Template" and "Number's" 1-17 in "Primary", respectively. 

 

Again, the end result is to have multiple separate "Templates", with data from "Primary's" "Trials" correlating to each "Number" in "Template"

 

Hopefully this will help a little bit David! If you or anyone else is willing to take a stab at my issue, I would greatly appreciate it! 

 

Thanks!  

DavidP
17 - Castor
17 - Castor

Hi,

 

I'm happy to work with you on this, so lets look at it step by step. Have a look at the attached workflow. It loads the primary tab, does a bit of tidying and transposes the data for Trial 1. Once we get it right for one trial, we can extend it to all trials.

 

So if I understand correctly, the data in the [Value] field is what you want to write to your template, right? If so, I notice that there are 2 fields for Examples 14, 15, 16 and 17. For some trials there are values in both. What do you want to do in this case?

 

Once we have the correct data set, it would be pretty straightforward to write it to the template and then save it as a unique file, but is there any specific reason you need to use the template? It would be pretty easy to recreate the format of the Final Examples tab for each of your trials using the Alteryx reporting tools and write them to individual excel files. Would this work for you?

 

One last question on the %After column:  I noticed the formulas for these cells are, Example1*1; Example2*2; Example3*4; Example4*5; Example6*7... Example17*18

 

Is this correct?

 

primary.png

 

 

jsheehan010
6 - Meteoroid

Hi David,

 

Thanks for your continued help!

 

Yes, you understood correctly! Ultimately, the data in the [Value] field is what I would want to write to the template. Additionally, for the fields that have two of the same [Values] in "Primary", I would like to combine them into the one. That way they will easily correlate with the "Examples" on the "Template." I think that this can be done by joining each common field and then renaming all of them to be consistent with the "Example" format in the "Template." Let me know if this makes sense to you?

 

As far as using the "Template" goes, it is complicated. Both of these excel files I've posted to the Community ("Primary" & "Template") are spin-offs of the actual files I need to use. They are very similar, although I just needed to extract some of the actual information for security purposes. In the actual template, the after% column's reliance is based on many more formulas in the "Extra Tab #'s" and the "Final Examples" tab is based off of "Manual Examples" tab. It is not as simple as Example 1 * #. I just did that for representational purposes, to show that one is reliant on the other. I did my best to emulate what I am actually dealing with and I think we're almost there. We have the logic of how to filter the information we need based on one "Trial" and should be able use that info to put into the "Template"'s "Manual Input" tab. Unfortunately, I'm still running into a few major issues.

 

Currently, I think those issues are:

 

1. being able to make it user friendly/interactive app to select the Trial's via a list box

2. produce multiple "Templates" based on however many "Trials" were selected via the list box

3. output the templates to the user's desktop (will be used by multiple users, not just myself)

 

I know it's easier said then done; but I think we'll be able to get it function properly if we both have an understanding of the ultimate result. 

 

Please let me know if I can further clarify any of what was previously mentioned, that will help aid you in this process. 

 

Thanks again for the help! 

DavidP
17 - Castor
17 - Castor

I'm splitting this into multiple posts because there are a few things happening here and if I lump them all together it could become a bit confusing.

 

If you have to write to an excel template multiple times, producing a new output file every time you have to do it in 3 steps.

 

1. Copy a "clean" template file to a temporary folder

2. Write your data to it

3. Copy the file to an output folder and rename it

 

This then becomes a repeatable process that can be wrapped in a batch macro and/or analytic app.

 

Step 1 looks like this:  I create a dos command that copies a file from one folder to a temp folder and execute it with the Run Batch command

 

step 1.png 

 

Step 2 is similar to what I showed before. There's a bit of logic that deals with the multiple columns 14-17 and it writes the output to a new tab in the template file called trialdata. There's a mapping in the template file that maps these 17 values to the manual examples tab. You can hide this tab if you want. Another way of doing this is to define a named range within the template file to write to, but named ranges rely on a column name for the mapping and in this case there is none.

 

step 2.png

Step 3 writes the template file to an output folder with a new name, using the Run Batch command again.

 

step 3.png

You have to make sure these steps are executed in the correct sequence, so I use block until done tools and a join and union tool in ways they were probably not designed to be used, but it works for me. So put it all together and it looks like this:

 

steps 1-3.png

In my next post I will convert this to a batch macro and then use the batch macro in an app with a list box to choose the trials that you want to select.

 

DavidP
17 - Castor
17 - Castor

So here is the previous workflow converted to a batch macro where the control parameter is a list of trials and the macro input is the data from 1 trial in this format:

 

macro input.png

primary to template macro.png

And here is the app with the list input generated from the trials in the primary file that is then passed to the batch macro as a list:

 

primary to template app.png

 

I'm attaching the whole lot and hopefully you can make sense of it and get it to work on your files. Let me know if you have any problems.

 

DavidP
17 - Castor
17 - Castor

Ok, here's the final bit.

 

In the macro, you have to define where the Temp folder is that you're going to write to (your users will have to set one up, or you can create one with another dos command and Command batch tool)

 

You also have to define where the Template file is.

 

These are both done in the Text Input tool like so:

 

filepaths.png

 

In order to write the output to the user's desktop, you can use the following formula to set the output path.

 

desktop folder.png

 

 

jsheehan010
6 - Meteoroid

Hi David, 

 

I think we are almost there! The only issue I am having is with the bat.file. I dont have access to it and I'm not familiar with bat files. Could you please further explain what you did here and/or provide that file?

 

Then hopefully I will be able to fully run the macro! 

 

Everything else looks like it should be able to function as I wanted. I cannot thank you enough for your help! 

Labels