on 05-26-2016 09:56 AM - edited on 07-27-2021 11:40 PM by APIUserOpsDM
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.
Finally, Year.
For times when there are 10 or more levels in a Tree Tool, please see the Spoiler below:
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
I appreciate the effort put forth to document this in great detail. Very useful guide.
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'.
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!
@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
@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
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
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?
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
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
@DataDynamite , this is great! Would certainly be worth adding the the Pre SQL Statement thought to the Ideas Page.
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.