Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

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

PeterS
Alteryx
Alteryx
Created

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 (Highlight to read)
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 toolto change the values to a string, then add a Multi-Field Formula toolto 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).
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 toolto change the values to a string, then add a Multi-Field Formula toolto 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.

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.

See how to put this into action in Part 2

Attachments
Comments
uebelein
6 - Meteoroid

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

RodLight
8 - Asteroid

@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'.

 

PeterS
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!

alonso
5 - 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

williamchan
10 - Fireball

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

PeterS
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

simon
11 - Bolide

I was looking at the help for Tree tool https://help.alteryx.com/2019.3/Tree.htm

 

specifically:

v: Element. Required.

n: Name that will be displayed. Required.

k: Value of the element. Required if the node is selectable.

l: Set to "F" to make element unselectable with a red X.

Any value assignment other than k or l will disable the selection with a grey X.

 

Can I add red X/grey X functionality using an odbc connection rather than a custom xml file??

 

thanks,

Simon

abell_dt
7 - Meteor

Is there a limit to what the interface can handle? Say there are 1000 unique values that can be included in a drop down. What's the limit?

DataDynamite
7 - Meteor

SQL to populate the tree interface on the fly:

 

WITH BasicQuery (Col1, Col2)
AS (
--Update here with your query, my tree just has two levels, follow the pattern to add more levels if needed
select distinct e.EmailDomain as Col1,
e.UserEmailID as Col2

from [jen_UserEmail] e

where e.IsVoid = 0

),

 

TreeWithKeys
AS (
select distinct Col1,
Col2,
RIGHT(CONCAT('00',ISNULL(DENSE_RANK() over (order by Col1),'')),2) as Level_1Key,
RIGHT(CONCAT('00',ISNULL(DENSE_RANK() over (order by Col1),'')),2)
+RIGHT(CONCAT('0000',ISNULL(row_number() over (Partition by Col1 order by Col1, Col2),'')),4) as Level_2Key


from BasicQuery
)

Select X.*
from (
select Col1 as [Description]
, [Level_1Key] as [Key]
from TreeWithKeys

UNION

select Col2 as [Description]
, [Level_2Key] as [Key]
from TreeWithKeys
) X
ORDER by 2

 

DataDynamite_0-1614298502579.png

 

 

With this you won't need a chained app or run a workflow to populate the .yxdb before using the interface.  This Tree is only two levels but should be easy enough to expand.  Peter, this is based off of your work, my interpretation of it in SQL while trying to make it as modular as I could.  It would be nice if the interface database connections allowed for a Pre SQL statement

PeterS
Alteryx
Alteryx

@DataDynamite , this is great! Would certainly be worth adding the the Pre SQL Statement thought to the Ideas Page.

The_Rad_Valentina
8 - Asteroid

THANK YOU! This is the most amazing article and I was able to solve my problem by following these directions. Created a workflow that users can run to get details of job runs in by collections.