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 Workflow to use a Tree Interface Tool

PeterS
Alteryx
Alteryx
Created

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.

example tree.png

Figure 1

So how can we use these selected values in a workflow?

As a review, our original data consists of very pretend pet data.

sample data.png

Figure 2

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.

2019-06-18_15-57-44.png

Figure 3

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:

example results from tree.png

Figure 4

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.

Initial set up.png

Figure 5

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.

filter example.png

Figure 6

Now let’s connect the Tree tool to the Filter tool and configure the Action tool.

filter with action tool.png

Figure 7

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], ' ', '","') + '")' 

action tool config.png

Figure 8

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:

111

26

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 , 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.

interface designer.png

Figure 9

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.

Attachments
Comments
AndrewPoole
7 - Meteor

I am working on an app using Tree where first level is a table then it should reveal the columns in that table.  The only difference I am dealing with on this is that I only need 1 key (table 1 = 1, table 2=2, etc, etc)  I can get the data out after selection in the app but I can't get to a point where I am able to select specific columns.  Any thoughts?

ecastruita
7 - Meteor

This is a great method for very small datasets. But for large datasets, this method is prohibitive in that it's basically duplicating the dataset it's intended to filter. What if you're trying to filter an InDB dataset with millions of rows?

Hiblet
10 - Fireball

This post really helped me out.  Thanks immensely for the thoroughness and details, and the example flows, much appreciated!

alv728
8 - Asteroid

@PeterS & @RodLight can you please share your solution for more than 9 values? I haven't been successful yet implementing the padleft() function. Thanks!

MichaelSu
Alteryx Alumni (Retired)

HI @PeterS , 

 

I am running into an issue where trying to leverage the tree interface tool to update a report header in the 'Report Text' tool. The issue I am coming across is that it updates the header to the key value, not the data itself. Wondering if you might know a workaround?

 

@lepome 

 

Thanks,

Mike

PeterS
Alteryx
Alteryx

Hi @MichaelSu,

 

That sounds like expected behavior. If you are directly taking the values from a Tree selection you will get a list of key values returned as noted in Figure 4 above.  Once those key values are selected they need to be parsed and then can be Joined or Filtered with the table that contains the desired data and Key values as noted in Figure 7 above.  Once that is done you can get the desired values to be used in the Header report tool.

 

Hope this helps!

nickscheil
5 - Atom

Hey @PeterS,

 

Thank you so much for the detailed descriptions/screenshots/examples! This has been hugely beneficial to me and my team!

 

Along with @alv728, I am having some trouble with the leftpad function, as I have more than 10 items in my level one grouping. Could you share a little bit more detail around how you managed to make it work?

 

Thanks!

 

UPDATE: spoke too soon! I wrapped the padleft function around the 'Level 1' field, before creating the keys so that the padding would flow down the entire workflow. Thank you!!

PeterS
Alteryx
Alteryx

@nickscheil 

Glad you got it working!

 

@alv728 and @nickscheil I have added a 'Spoiler' section to this article https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Configure-a-Custom-File-Tree-Data-S... that describes what to do when there are 10 or more levels.  Hope this helps!

daviskb
7 - Meteor

What if:

1) You have 20,000 names in the initial DB

2) You want the user to enter a value to filter the 20,000 names to a smaller set

3) You want the user to select from the filtered set -and-

4) You want to use this in a multi-user server analytic app

 

I can figure out how to do the first 3 but I'm stumped on the last step.  Thanks for your consideration!

_sbeaumaster
7 - Meteor

Can you run Part1 and make it part of Part2 or do you have to run it twice? 

 

PeterS
Alteryx
Alteryx

Hi @_sbeaumaster,

 

In Figure 3 above, adding the Transpose tool could be added to Part 1 of creating a custom Tree Interface. The values that populate the tree to have to be created prior to running the app since that file needs to be used to configure the Tree Interface tool as seen in Figure 5, unfortunately the data that populates the interface cannot be made 'on-the-fly' in the same app. However, you could potentially use a 'chained app' to first generate the data in Part 1 and then have the second app in the chain use the data from the first app. This would be the case if your Tree values are changing from time to time.  If they are static values you could just make the list once and not have to worry about Part 1 after your values have been created.

 

Hope this helps!

daviskb
7 - Meteor

Following up from my previous post:

4) You want to use this in a multi-user server analytic app

 

I ended up creating a unique .yxdb file for each entry that represents the "record" to be inserted into the database.  Then twice a day (early and late in the day) a job on the server collects all the .yxdb files and updates the master database.

julcsi
5 - Atom

Hy @PeterS ,

 

Thanks for this amazing example!

Do you know how this can be implemented into a Snowflake(SQL) script?

I was wondering if there is an alternative solution for adding the Tile tools, because for larger datasets and larger hierarchies the WF runs very slowly.

vlad_kutateladze
8 - Asteroid

Hi all, 

 

Maybe you will be interested. 

 

I have created the macro that generates keys for the Tree interface tool. And it can create hierarchy levels as many as needed. So if you have a different number of hierarchy levels for different apps, no need for modification. 

 

Link to the Gallery:

https://community.alteryx.com/t5/Community-Gallery/Tree-Tool-Keys-Generator-Macro/ta-p/1040818

 

 

 

vlad_kutateladze_0-1669819690295.png

 

Capturgdse.PNG

 

vlad_kutateladze_1-1669819724886.png

 

 

 

 

vlad_kutateladze_2-1669819779876.png

PeterS
Alteryx
Alteryx

Very cool @vlad_kutateladze ! Makes the key creation process so much easier!

vlad_kutateladze
8 - Asteroid

@PeterS Thank you! I am glad you liked it. 👍

cfvito
7 - Meteor

@PeterS your post was really helpful, thank you. I was able to build the Tree Tool, by following your instructions however, for some reason the order appearance was not sorted in alphabetical order as I expected. Also, there was one parent category that was aligned under another parent level. 

 

Below is a sample of the appearance, due to security reasons I am not able to provide the actual data. Instead of the Fish and Bird being a parent level, it went under name "Cat".

 

cfvito_0-1679428577338.png