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.
How To: Change Date Type Coming Out of Date Interface Tool to Match In-DB Field Date
A field in my database has dates in the following string format: YYYYMMDD. However, the date format coming out of the Date Interface tool is YYYY-MM-DD. How can I change the date format coming out of the Date interface tool to match the date field in my database? I am pulling the data In-DB, so I don't want to change the date format of my data, which would require me to pull the data out of the database, hence, slowing things downs tremendously.
Product - Designer
In the Action Tool created between the Date interface tool and the Filter In-DB tool, choose the 'Update Value with Formula' action option.
Select the 'Expression - value' in the 'Value or Attribute to Update' section.
In the Formula section at the bottom of the Action tool configuration window, add the following formula: '"DateField"' + "=" + "'" + REGEX_Replace([#1], '-', '') +"'"
This formula will remove the dashes in between the year and month and month and day from the date passed in by the Date interface tool in order to match the format of the field in the database. You can use other formulas, such as DateTimeFormat or DateTimeParse to modify the Date interface format to other formats as well.
The Tree Interface can be a useful way to allow a user to select input values for an app. Since sometimes setting up the Tree File Data Source can be a little tricky to those new to this interface, this example will step through the creation of the Data Source for a simple Tree Interface.
To use a Custom File or Database as the source for the Tree Interface tool, the source needs to be in a specific format: a table with a Description and a Key field. It will look something like this:
And result in an interface that looks like this:
Let’s start with our original data, some very pretend pet statistics:
The first step is to determine the fields that will be used in the interface; in this case, we want to be able to select the fields Pet, Breed, and Year in the Tree Interface. These will become the different levels in the interface.
Next, we need to determine the level in which our fields of interest will appear in the tree -- the Pet field being the most general and Year being the most detailed. Sorting in this order will make sure all the data is aligned properly. All the Cat records will be together, then all the Breed records will be together, and finally all year records.
Once the data is sorted, unique identifiers need to be created for each record to establish the hierarchy.
The first level in the hierarchy is the Pet field. To create an identifier for each unique value in the Pet field, the Tile tool can be configured to look for Unique Values on the Pet field. The unique value for the Pet field will be in the Tile_Num field.
This process needs to be repeated for the Breed and Year fields. The Tile tool for the Breed field will again be configured to Unique, but this time under Unique Fields Pet and Breed will be selected.
For times when there are 10 or more levels in a Tree Tool, please see the Spoiler below:
If there are 10 or more levels that need to be created for the Tree one more step will need to be done in order for the Tree Tool to differentiate each level. For 10 or more levels you will want to apply the PadLeft() function to each level to give a buffer to each value for each level. After the Tile tools used to create the key for each level use a Select tool to change the values to a string, then add a Multi-Field Formula tool to apply the PadLeft() function to each Tile_Num field: Adding the padding to the front of each level number will allow the concatenated values of all levels to be unique and should not cause issues when working with 10 or more levels in a Tree Interface. An additional workflow is attached that demonstrates adding the Padleft() portion, Tree Data Source 10 or more levels.yxmd (version 2018.4).
A little cleanup was done using a Select Tool. Three things were done: all the Tile_Sequence fields were removed, the Tile_Num fields were renamed to the appropriate Level number to make things a little easier in subsequent steps, and each Level field was changed to a string so they could be concatenated in the next step.
Now we have all the information we need to build a key for each level of our data for each record. A Formula tool is used to create 3 new fields, one for a key at each level. Our Level 1 Key will just be the value from the Level 1 field. The Level 2 Key will be the Level 1 Key concatenated with the Level 2 field, and the Level 3 Key will be the Level 2 Key concatenated with the Level 3 field.
Now that there is a key for each level we can construct a table that has the necessary Description and Key fields. Using a set of Select tools, select each Level Key field with its corresponding description field; Pet and Level 1 Key, Breed and Level 2 key, Year and Level 3 Key.
Now Union the three Select tools together by position. Add a summarize tool and group by Pet and Level 1 Key. Grouping by these fields will remove any duplicate records. The resulting fields were also renamed to Description and Key in the Summarize tool.
Finally, a Sort tool is added to sort the Key field in ascending order. The results from the sort can then be saved to a file or loaded into a database and used in the Tree Interface tool.
The entire workflow:
Attached is the workflow used to build the Key list, as well as a very simple app that demonstrates the resulting tree created from the key values.
See how to put this into action in Part 2
So we’ve generated our list of Key Values (in Part 1) to be read by the Tree Interface tool and when we look at the Tree Interface it looks correct.
So how can we use these selected values in a workflow?
As a review, our original data consists of very pretend pet data.
Now we need to associate the Key Values to our data so that our data can be queried. This can easily be done by adding a couple steps to the workflow we create in Part 1 (updated workflow is attached).
By adding a Transpose tool, the table can be manipulated to get each possible Key Value for each record. Then a Select Tool to rename the Value field to Key. Those results are saved to a new file (Pet data with key values.yxdb) that will be used in the app.
When values are selected in a Tree, a list of the Key Values are returned with a line break between each value. In the above Tree Interface example (Figure 1) the Key Values 111 and 26 are returned as a string data type and would look like this:
Setting up the App
We now have all the parts we need to create the app: Pet Key values.yxdb (from Part 1) and Pet Data with Key Values.yxdb
The start of the app is a Tree Interface tool that points to the Pet Key Values.yxdb file, and an Input tool pointing to the Pet Data with Key Values.yxdb file.
We know the Tree Interface returns a list of Key Values, so we can then filter our pet data on the Key Values. A Filter tool is added with a template filter that will ultimately be updated by an Action tool, once we connect the Tree Interface to the Filter tool.
Now let’s connect the Tree tool to the Filter tool and configure the Action tool.
We are going to select the Action Type ‘Update Value with Formula,’ and update the full Expression. Now for the Formula: click on the ellipsis (…) to add this formula:
'[Key] in ("' + REGEX_Replace([#1], '\n', '","') + '")'
This expression will build an IN statement (Figure 5) based on the selected values from the Tree Interface. If the returned values from the Tree tool are, as in Figure 4:
The expression will build a string starting with: [Key] in (“
The REGEX expression looks for all line breaks (or newlines) in the connection 1 ([#1]) value, denoted by the \n, and replaces those occurrences with: “,”
And then finishes the expression with: “)
The final expression looks like this: [Key] in (“111”,”26”)
This will replace our template expression when the app is run.
To test your Action tool expression, use the Interface Designer’s Debug tool. Open the Interface Designer from the View menu in Alteryx. This Community article goes into more detail on how to use the Debug tool.
With that, the tree tool is complete. The Key Values from the selected Tree Interface items are used to select the desired records from the dataset for further processing.
Add a Browse tool to the True output of your Filter to view the results when the app is run. Be sure to configure your app to show the results of the Browse when the app completes. This setting can be found in the Interface Designer, under Properties.
As with many things in Alteryx, there is usually more than one way to accomplish a task. In the attached App example workflow, there is an additional method to take the results from the Tree tool and query the pet dataset.
All related workflows are attached to the post and saved in Alteryx 10.0 format.
Building out a workflow and find yourself stumped when trying to add needed functionality to your process? While the Designer does an incredible job of packaging just about every operation an analyst could need, you might need that extra mile. We get that. Here at Alteryx we are all about going that extra mile; if we don’t have a tool that doesn’t explicitly capture functionality for you, we try to equip you with tools that can make for an easy reach to that functionality from resources just outside of the Designer environment (see our R Tool, the API based Connector Tools, and the Run Command Tool). In this article we’ll go over an introduction on how to make and use batch files – these will easily incorporate command line based scripting into your workflows that will help you do just about everything short of feeding your dog.
In short, a batch file is a plain text file that lists a series of commands for the command line interpreter to run in Windows. They’re frequently used to make, remove, rename, move, or even copy directories or files, ping IP addresses, run other programs or services, and manipulate environment variables – and that’s hardly even the tip of the iceberg. There’s a wealth of resources online listing the different batch commands available to you, but the Ben/Peter Parker rule applies to them all: “with great power comes great responsibility.” Please use them responsibly!
To make a batch file, all you need to do is take your desired batch command(s) and write them into a text file:
Be sure to take note of the paths being used! This batch command will make a directory named “batchfolder” in whatever directory it is in when run. Before it can be run as a script, however, we have to save it as a .bat file:
In “Save As” change the “Save as type” to “All Files (*.*)” and change the “File name” .txt extension to .bat:
In your working directory you should now see a different icon, extension, and type, describing the file:
How easy was that? If you want, you can test to see if it works by navigating to the directory the batch file resides in and typing its name into the Command Prompt:
You’ll now see the directory we made with the execution of the batch file:
See, I’m not making this stuff up. Now let’s get all this set up to run for us in the designer. All you need to do is specify the .bat file name in the “Command” configuration option - by default, it will look for this file in the directory the workflow is saved in:
In the attached example, I use the Run Command Tool as an input of a test file (specified in the “Read Results”) before writing the file to the new directory made from executing the .bat file.
When creating custom Tree questions in Alteryx, the Module or Macro designer will typically use the “Custom File/Database” mode of the tool. This is a great option because of the ease with which the data can be arranged to populate a great Tree question. Handling the Action side of a Tree question can sometimes be a little challenging, and the goal of this macro is to streamline that process.
Consider for a moment the following example where the user has selected “Blue Whale” from the Tree. Would you like just the record for “Blue Whale”, or would you like the parent records from that selection? Depending on your choice in the Tree Processing Macro, you can receive the following outputs from the tool.
What if we had selected “Whales”, how would you like to handle the child records from that selection? The Tree Processing Macro makes it is easy, as it will return all of child records for you automatically.
The macro is designed to reside in the .yxwz from which the user will interact with the Tree Question. The same file that is referenced in the Tree Question should also be present as an Input tool. This Input tool will populate the connection to the “D” (Data) input of the Tree Action Macro. The “T” (Tree) connection will be populated by a connection from a Text Input tool. As every Question has an accompanying Action, your .yxwz's Action will update the Text Input tool.
The Tree Action Macro has two configuration settings; the first defines whether you wish to exclude branches as records in the output. The second setting allows you to define the key length. Apart from these settings, the Tree Action Macro will take care of the rest.
I have provided a sample Analytic App that details the use of the Tree Action Macro, and the difference between including branches as records and excluding them.
Recently, I was asked if it was possible to have a question included in a macro that would not show to the user in the configuration window.
In this case, the user was trying to create a random seed value to pass into the Create Samples tool. The way he had it worked out was to create the random seed value in a formula tool and then pass that into a Drop Down question to update the Create Samples tool.
This method worked just fine, except it showed an extra question in his macro configuration that he didn’t want the end user to see since they couldn’t change anything.
As it turns out, you can use an Action tool without a question to achieve this task. Simply connect the Action tool (in the Interface category of your palette) to the desired tool - in this case, the Create Samples tool -- and configure it as planned.
In the attached sample, the macro takes some dummy input and creates a random seed value. The macro itself when run as a workflow won’t return any results, but you can open the Interface Designer and select “Open Debug” to view the random seed value change.
Workflow and Action tool Configuration:
As viewed in Interface Designer - Open Debug:
Download the attached workflow and try for yourself!
Some users have reported a problem when importing and exporting macros within the Alteryx Designer when the Regional Settings for the machine are not set to English (United Kingdom) or English (United States) (see screenshots below). You may also be unable to see personalized Macro folders within the Designer. They encounter the following error: "Input string was not in a correct format.”
The workaround for this is to switch the Regional Settings to the regions named above and you will then be able to import, export macros and see personalized macro folders within the designer categories.
The screenshots below demonstrate what the users will see when they try to import and export workflows with other Regional Settings than English (United Kingdom) or English (United States).
To find Region and Language settings in Windows 7, please navigate to the Control Panel>>>Clock, Language and Region>>Region and Language. For more information on Localization please see this link: http://community.alteryx.com/t5/Analytics-Blog/Alteryx-Commences-Localization-Endeavors/ba-p/11765.
The key component of any batch macro , the Control Parameter Tool is the gear that keeps things moving. Using the input , the control parameter accepts a field of values that will be used within the batch macro to reconfigure and rerun the macro for each of the standard input records - unless using the GroupBy feature that matches certain control parameters to buckets of records to be batched through the macro together. Adding this interface tool to any macro will upgrade it to a batch macro and will give you the ability to loop through macro configurations for added customizability. While one of the more sophisticated solutions you can build into your workflows, there are few problems you can’t solve with a batch macro:
A must-have for any app or macro, the Error Message Tool displays a prompt to the user based on input from Interface Tools . Using any expression that evaluates to true, along with any number of user inputs from question anchor connected Interface Tools, the Error Message Tool can layer even the most involved applications with failsafes that ease a user’s experience through more robust interactions.
Alteryx Analytic Applications (Apps) let us take a process, parameterize parts of it, and add an interface so that end users don't need to know all of the inner workings of the process in order to make it work for their specific scenario.
For any macro or analytic app – one of the inevitable questions that you may encounter is “how do I configure this to do what I need?” For example, if you build a macro that checks if two fields are equal, but sometimes you want to ignore the case such that “A” equals “a,” and sometimes you want an exact match. This is where the Interface Tool Category comes to the rescue, with a super-tool called Check Box!
The Drop Down Tool is part of the Interface Tool Category and can be used when creating Apps. This tool has many great configurations from loading data from within the workflow to using outside sources to update tools. Hopefully, after reading this article and looking at the samples you will feel more comfortable using this tool in your app user interface.
Question Have you ever wanted your own help page for your custom macros or applications?
Answer If you create your own macros or applications and send them to other who aren’t as familiar with your project, or if you just need a refresher from time to time, you may try and access the help menu only to be greeted by the general Alteryx macros/apps pages:
Macro Workflows Page
Analytics App Workflows Page
You can actually create your own help pages/files that can be accessed how you would normally access the Alteryx Help Menu for any "out of the box" tool that comes with the Designer.
Using your favorite text editor (Microsoft Word, for example), you can create your help file with any instructions or graphics that you feel would be helpful to the end users who may need to access a help file. Once you are done, you can save this in any file format that your (or your users') machine is able to open, as well as any location those users would be able to access (a network drive for example).
In your application or macro’s Interface Designer Properties, there is an option to add the path of a file or hyperlink to your newly created help file.
For an example I created the following help file as a .docx, .pdf, and .htm file type. Each other these files open in their respective default programs.
In a previous article we’ve shown how the list of selections in the List Box tool can be dynamically generated from:
the column names from an input file
the values in one of the columns of the input file
the values in one of the columns of a user-selected input file (target field must be in both original and new file)
In this second article, we’ll see how to let the user select both a file and a field within this file to create the values in the List Box. The file that the user selects doesn’t need to have the same columns as the one you used when building the App.
Show the selection options from one of the columns of a user-selected input file and field
In this example we will use the techniques we’ve explained in the first article including, reading field names from input, creating value list and using chained Analytic Apps to show the second screen with the list of values for selection. Again, make sure you save these workflows as Analytical Apps.
The first workflow uses what was done in the previous article but relies on the fact that Alteryx yxwz files are XML files containing the configurations of the tools of the workflow. To load the user-selected values into the second List Box, we will manually update the XML of the second workflow to change the List Box configuration. The List Box tool, when set to Manually set values, expects a Name:Value pair and therefore we are using the Multi-Field Formula tool to create these pairs and change the field size to make sure it fits the extended text. We then use the Summarize tool to concatenate all of the separate values into a single field with new-line (\n) as separator.
Now that we have our List Box Values ready it’s time to load them into the second workflow. For this to work, we should have already built the second workflow, ProcessWorkflow.yxwz and setup the List Box tool List Values to Manually set values with the value set to _xxxx_:xxxx.
Back to our first workflow, we start by reading in the second workflow we just created. To be able to read it into Alteryx, we set the Input Data tool to read the file as CSV with \0 as the delimiter (no delimiter) and with first row contains field names.
We then use the Summarize tool to concatenate the rows into a single row and make sure you name the field <?xml version="1.0"?> (this will be the first line in our output file).
We bring in the list of values we created in the first part of the workflow and append it to this data stream using the Append Fields tool and then use the formula tool to find the _xxxx_:xxxx text and replace with the correct values. We deselect all fields except the <?xml version="1.0"?> using the Select tool and write the output to a new file ProcessWorkflow2.yxwz using csv as format and \0 as delimiter.
The last step in this workflow is to set the new workflow we just wrote-out to start when the first one finishes execution. This is done by naming the second workflow in the Interface Designer (Alt-Ctrl-d) under Properties: "On Success – Run Another Analytic App". Also the "On Success - Show Results to User" should be deselected.
You can find the workflow used in the article attached to this post. It was built in Alteryx Designer 10.6 (10.6.6.17413).
Special thanks to @patrick_digan for testing and suggesting improvement to the example workflow.