This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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).
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.
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!
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
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.
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 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 3 writes the template file to an output folder with a new name, using the Run Batch command again.
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:
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.
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!