Alteryx 2011 brings a great new feature to the wizard or macro creator's arsenal: the Tree question type. It allows you to present a selection of options to the user in a hierarchical structure.
It is added in the same way as any other question:
Data Source
It has five options in terms of choosing its source data:
- Allocate Geography
- Allocate Variables
- File System Directory
- Custom XML
- Custom File/Database
Allocate geography and variables will display tree views of the allocate geographies or variables from the allocate data sets on a users machine:
File System Directory will display a tree view of files in a folder (Note: Only Files are selectable):
Custom XML lets you define your own tree using a XML file. The XML needs to have the following structure:
The n attributes are the display names and the k attributes are the key values which get passed to your module. The v elements define the tree structure or hierarchy.
Which results in the following tree structure:
The final option allows population of the tree from a custom file or database. The structure needs to be like the below:
This gives the same tree as with my custom XML example above.
Note: There is an important difference between using a file/database source and a XML source. In an XML source you define the tree structure using the v elements; the names and keys can be whatever you want. In a file/database source it is the key values which define the tree structure; so in this example the fact that 0 is a substring of 01 means that Delaware sits under USA in the tree structure.
Additional Options
There are two additional options to set up.
- A check box which determines whether a single or multiple items can be selected.
- A numeric value which determines how many lines the tree will display before needing to scroll.
Return Values
If you are using an allocate data source for your tree there are two options for the return data, selectable from the question's properties:
Save Values as XML - returns values in an XML format suitable for updating allocate tools using an "update raw XML" action.
Save Values as Allocate Workspace snippet - does what it says and can be used to update allocate tools via an "update/change value" action.
If you have a File System Directory data source the selected files are returned with full paths separated by new line characters. This could be used to update a dynamic input using a text to columns tool as in the below example:
When you get to a custom data source things begin to get a little more challenging for the macro/wizard developer. The difficulty is that the question will return the minimum possible data to represent the options selected and does not return any indication of hierarchy. So still using my custom XML example above:
Returns
011¶013¶021¶024¶025
Returns
01¶021¶024¶025
Returns 0
Now if you have data at County level, which you wish to filter based on the user's selection how do you deal with these different cases?
The best solution I have at the moment is a filter updating with an "update/change value" action using "Update using a formula" with the below formula:
'[Key] in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")' +
' or left([Key],2) in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")' +
' or left([Key],1) in ("' + REGEX_Replace([Custom XML Tree View], '\n', '","') + '")'
The REGEX_Replace([Custom XML Tree View], '\n', '","') part converts the newline separated list to a comma separated quote qualified list and the three "in" statements check at the three levels of the hierarchy.
As always if you have a better solution or any other comments feel free to post below.
