community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Configure a Custom File Tree Data Source for a Tree Interface tool

Alteryx
Alteryx
Created on

 

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:

 

 

2019-06-18_16-46-43.png

 

And result in an interface that looks like this:

 

 

2019-06-18_16-45-42.png

 

Let’s start with our original data, some very pretend pet statistics:

 

 

2019-06-18_16-47-17.png

 

 

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.

 

 

2019-06-18_16-48-39.png

 

 

 

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.

 

 

2019-06-18_16-52-21.png

 

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.

 

2019-06-18_16-53-20.png

 

 

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.

 

 

2019-06-18_16-56-08.png

 

 

Finally, Year.

 

 

2019-06-18_17-00-48.png

 

For times when there are 10 or more levels in a Tree Tool, please see the Spoiler below:

Spoiler
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:

10 or more leves.jpg

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. 

 

 

2019-06-19_7-55-33.png

 

 

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.

 

 

2019-06-19_7-58-47.png

 

2019-06-19_7-59-55.png

 

2019-06-19_8-00-56.png

 

 

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

2019-06-19_8-02-24.png

 

 

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.

 

 

2019-06-19_8-04-46.png

 

 

The entire workflow:

 

 

2019-06-19_8-05-28.png

 

 

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.

Attachments
Comments
Alteryx Partner

I appreciate the effort put forth to document this in great detail.  Very useful guide.

Alteryx Certified Partner

@PeterS

Using the Tile tool is clever, but I'm finding that if my hierarchies have more than 9 elements (especially the first level), there seems to be no way to differentiate within the Tree tool whether '10' belongs under the '1' hierarchy, or should come after '9' in the first level.

Any thoughts?

 

Appending to this comment...

I think I have figured it out. What I did was determine the maximum length of each level's key and PadLeft a '0' to each applicable level. This creates a key that then will have leading zeros so you don't have the case of '10' belonging to '1' in the hierarchy since it becomes '01'.

 

Alteryx
Alteryx

Hi @RodLight,

Yes, the 'padleft' would resolve that issue.  That is actually something that would need to be done in the workflow when there are 10 or more levels to the tree. The PadLeft function would be used in a Formula tool after the Tile tools in the above example for each of the keys (level 1, level 2, level 3...) created Thanks for posting your solution back to the post for others to reference!  Have a great weekend!

Atom

@lightalytics please share with us if you find the way to solve that, I have a data sets of hundreds and I need to solve that  issue too

Hey @PeterS & @RodLight ! Thanks for sharing this detailed Use Case! I was able to use it for a project of mine. Came across the issue of having more than 9 elements too and padding the string helped solve my issue. This should be in Tool Mastery if its not already!

 

Cheers!

William

Alteryx
Alteryx

@RodLight , @alonso , @WilliamChan, for your future reference have have added a section above that describes how to handle situations where there are 10 or more levels, it is under the 'Spoiler' section  Hope this helps. 

 

Peter