Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hi
I am trying to set up a drop down tool so that when the end user uses the app they have the choice of picking one of the five filenames [filename column]. I have experimented with an interactive drop down tool but the issue is how do i link it back to the input file to progress the workflow.
Thanks!
Solved! Go to Solution.
hey @ManuelRodrigues
Great question!
I'll take you through each step to produce this, and because i dont have access to your folder, i have created this using a folder on my Desktop for demonstration purposes. However, this will work no matter where you point the directory tool.
I would recommend, if you haven't already, taking a look at the Analytic App Interactive lessons:
https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%2...
This will make it easier to follow what i have done for this process.
So, you've currently got:
As you can see, I have an error on the directory because i dont have access to that folder. Not to worry, i'll set that to a folder on my desktop just by changing the directory path:
With the results:
(I just made 4 empty text files for a demonstration).
So you want to make an analytic app from here, giving the user a choice of what file they want to process/choose. The first place to start, is the interface tools:
You can see we have a whole plethora of tools to choose from in here. These are what we use to create a user interface for our workflow, or an 'Analytic App'.
In this case, you want a dropdown tool, so I'll drag a dropdown tool onto the canvas:
Now, two things to notice here. There is now a 'wand' icon next to 'Run'. This indicates that we are now building an Analytic app:
But also in the configuration window, we can specify what values we want to use from this dropdown tool:
The one we want to use in this case, is 'fields from connected tool'. We want the dropdown to reflect what is currently in the workflow, at a current point.
I am also going to change the text from "Drop Down (17)", to reflect something a little more user friendly:
So now we need to connect our data to our dropdown. Now something to bare in mind with the interface tools, is that it expects the options you want the user to be able to select to be the columns of the data you input into the dropdown tool. You can see this by opening the example workflow for the dropdown tool:
But as a visual aid:
Highlighted, connected to the dropdown, will become the options for the user.
In our case, we have our file names in different rows:
So we need to first transform the data using a Cross Tab tool, so we can have those values as columns. I will setup my crosstab as follows:
Bare in mind that the 'values for new columns' actually doesn't matter here, you can select any of the values. Its simply the 'change column headers' that is important, for making our rows into columns. Our new data is:
(Exactly what we need, file names as column names).
We can then connect this cross-tab to the top input anchor of the dropdown tool. Think of top and bottom inputs in interface tools as left and right input and output anchors of normal tools respectively.
We can then click into the white space of the canvas, and then hit the magic wand next to run:
Exactly what i wanted!
Last step is to make sure this changes what we filter on. So as mentioned, the bottom anchor of the interface tools are the output. And we want to output to our filter tool. Now you will notice above our filter, two inputs:
A "Q" (Or magnifying glass, theres a bit of a debate), or a lightning bolt. In this case, we are going to use the Q, as its a lot easier. However, if you're interested in the difference, @MichaelSu explains it great here:
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Difference-between-Input-Connector-and...
So, lets connect our dropdown and get it working:
Now that they're connected, we can use the value passed from the dropdown, in the filter tool. This is done by referencing [#1] in this case. If you have multiple interface tools connected to the same tool, they can be referred to by their connection number ([#2]...[#3]...).
So we can change our filter to:
and what i'm expecting this to do, is when the user specifies a value in the dropdown, it will only passthrough values that have the same filename as that selected. Lets give that a test to be sure.
We will use a trusty browse tool:
And ensure that when this app runs, the results of the browse tool are shown to the user. This can be done by going to View - Interface Designer:
Which will open up a second configuration window to the left of your screen, specifically for creating analytic apps and interfaces. There is more on this here if needed: https://help.alteryx.com/20214/designer/interface-designer-window
Within the bottom option, the cog (highlighted yellow):
We can see the option:
I'm going to tick this, to ensure that when the workflow has run, it will present the browse tool to the user.
Then, back to our magic wand:
I'll test it with test_file_3
Sadly this didn't work. This is because (and you may have noticed), the Cross-tab has removed the spaces and "." from my file name. We can fix that in two ways (fix the text going into the dropdown field, which I would do through a not so clean formula in a dynamic rename tool, or we can do it a bit of an easier way, and convert the text going into the filter tool to be the same. I'm going to go with the easier route but if you're interested in the former, let me know!
So i want to convert my "FileName" to the same format as what is in my dropdown (test file 1.txt --> test_file_1_txt). I'm going to use a formula in this case:
So i have created a new field, and replaced all " " and "." with "_" (the same as the crosstab).
I can then use this in my filter:
And we can retest!
and now my app results are:
My file 3!
You can then create the rest of your workflow exactly as you want. The step we did previously to make the browse tool the outcome for the user, we can also do for render/output files too.
I hope this was helpful (and not *too* long winded, I'm just enthusiastic about apps 😁)
Please find attached my workflow. If you have any questions let me know!
Cheers,
TheOC
@TheOC I have a few questions on this. I've tried to follow you example and the app basically results in one of the issues that you had before using the formula and filter tools:
one other thing that i should have mentioned is the reason i am setting this up is so when some one picks the file name it also picks up the full path for that file and draws down the data into an input tool or dynamic tool. I've tried using the dynamic tool and although the schemas of all four files are the same i can't seem to get it to work.
Your help is greatly appreciated!
hey @ManuelRodrigues
Not a problem!
I believe i can see what the issue is - you have other symbols within your files, that will be also translated to "_" by a cross-tab tool.
You will have to add these to the formula, where we replace symbols:
so change " ." to " .-" (and add any other symbols that are contained within your file titles).
Once you have one file filtered, you should be able to input this into a dynamic input, with a template selected, ensuring that you are changing the file/table name with the fullpath:
I know you mentioned that the schemas are the same, however if they do change, i would recommend having a look at @mceleavey 's multiple schema tool:
https://community.alteryx.com/t5/Public-Community-Gallery/Import-Multiple-Schema-Excel-Files/ta-p/91...which does a similar thing, but removes the need for setting a template.
Do you receive an error when trying to use the dynamic input? Or is it just not outputting any data?
Cheers,
TheOC
Hi @TheOC
Interface tools get populated before the workflow runs. In most cases this isn't a problem, since the values displayed in the Interface tools are read from external sources, or don't change from one run to next. In this particular case, the values displayed in the dropdown come from metadata created from the files available when the workflow is saved. You can see this in this image. The app was saved when there was only 4 csv files in the directory. I added another csv file to the directory, but it's not showing up in the dropdown.
The only way to get this to work truly dynamically is to use a chained app where the first one saves the file list to a .yxdb and the second one uses this .yxdb to populate the dropdown.
The other option of course is to use a file browse tool with a wild card filter set to "*Previous Qtr Transactions*.*"
Dan
@TheOC managed to sort the issue with additional symbols. I think i am getting there any ideas how i configure the multi schema tool?
hey @ManuelRodrigues
Sorry about delayed response - you need to connect what is coming out of your filter to both inputs of the tool. Then go into the multi-schema tool configuration, and hit "questions":
Here you can specify the Full Path field (Likely to still be called "full path"):
This should import your data without a template. Let me know if it works for you.
Also thanks @danilang ,
error on my side there. Yeah we may have to convert this to a chained app @ManuelRodrigues , especially if you want that list to by dynamic when you add more excel files to that folder. Happy to talk you through that process too!
Cheers,
TheOC
@TheOC No probs re delays. I have connected the filter outputs to the batch macro but i get the no outputs msg?
