AppCenter Topics: Creating a Drop-Down Menu - How do you solve a problem like "Beyoncé"?

Learn Data Science
Teradata Employee

Suppose that you work for a business that runs a music streaming website (of course, the AppCenter topic that we are about to discuss can be applied to other industries, as well). The business sponsor wants you to build an AppCenter app that allows a user to type in a music artist’s name and then generate a sigma chart showing “related” artists that fans of this artist listen to on your company’s website. You build an app that has both a user interface (UI) and an input table with data from your company’s website. In the UI, you set up the “artist” parameter as a text field, thinking that everything should be fine.

While testing your app, you say to yourself, “I think I will generate the sigma chart for ‘related’ artists that Beyoncé fans listen to.” You type “beyonce” into the “artist” field in the UI, and the app runs without errors, but no visualization is generated. You wonder if this happened because, maybe, her name is spelled “beyoncé” in the input data set, with the “é” character at the end of her name, instead of just “beyonce”, which apparently resulted in no matches with the input data set (since no visualization was generated when you ran the app). You search the internet and find a resource that tells you how to generate special characters with your keyboard, come back to your AppCenter app, and type “beyoncé” into the “artist” field in your app’s user interface. Success! The app runs and produces the correct sigma chart.

Then, you start to think, what if my end user does not have time to look up ways to generate special characters with their keyboard? What if they misspell or mistype what they are looking for? And what if they enter a value that does not exist in the input data set? To avoid these issues, AppCenter allows you to create UIs with drop-down menus that show your end user lists of ready-made choices that they can pick from to fill out the fields of interest.There are two methods for creating a drop-down menu in an AppCenter app that has a UI: one method references a column in the input table, while the other method allows you to manually create the choices for the end user.


Method #1. Referencing a Column in the Input Table

If you want to limit the end user's choices to the items in a particular column of the input table, do the following:

  1. Go to the AppCenter home screen and click “Build an App.”
  2. Under the App Info tab, enter the requested information. Make sure that the boxes for “UI” and “Portable” are checked.
  3. Go to the Input Tables tab and choose your input table of interest.
  4. Go to the Input Parameters tab. Click "Add Parameter." You will see this dialog box:

Dialog Box - Input Parameters Default.PNG

   Figure 1. Input Parameters tab for the Build an App dialog box


   5.  In the fields that appear, enter the following (items in bold are required):

    • Type: Column Values Selector
    • Name: What you will call the parameter in the code under the Logic tab.
    • Label: (optional) The parameter name as you want it to appear in the UI.
    • Default Value: (optional)
    • Description: (optional) Short description of the parameter.
    • Requires column: (optional) Leave this blank.
    • Tab: (optional) Allows you to create separate tabs for different parameters in the UI.
    • Optional parameter? (optional checkbox)

       When you select “Column Values Selector” for Type, the dialog box changes to look like this:
Dialog Box - Input Parameters Additional Settings.PNG  

   Figure 2. Input Parameters tab – Additional settings for type: Column Values Selector


       Additional Settings fields now appear. Fill them out as follows:

    • List values from column: Choose the column from the input table that you want to use as your drop-down menu.
    • Filter table rows by parameter values: (optional) Creates another field in the app’s UI that allows the end user to filter the “list values from column” values according to other variables in the input table.
    • Allow input by free typing? (optional) Checking this box allows the end user to enter anything for the parameter, regardless of whether or not it appears in the input table (they will still see the drop-down menu when they are entering a value, though). Leave this box unchecked.
    • Allow multiple selection? (optional) Checking this box allows the end user to enter more than one item in the parameter field. Leave this box unchecked, for now.


NOTE: If you check the “Allow multiple selection?” box, the code under the Logic tab must be able to accept multiple inputs for the parameter of interest. Otherwise, you get a syntax error when you run the app.

   6.  Go to the Logic tab. Make sure that all input parameters are in this form: ‘${parameter_name}’

   7.  Click “Save,” then configure the app as you would normally.

To demonstrate this process, let’s go more in depth with our example from earlier. We will use an open-source data set supplied by Last.fm, a music streaming website, in order to create our sigma chart app. The data set can be downloaded here: http://ocelma.net/MusicRecommendationDataset/lastfm-1K.html. This data set includes user ids, artist ids, artist names, and number of plays prior to May 5th, 2009 for about one thousand users of the Last.fm website.

Once you have downloaded the data, load the data into a Teradata Aster Database. To do this, first create a table using the following SQL code:

     --Create table (drop table first, if it already exists)

     DROP TABLE IF EXISTS [schema].onekusers;

     CREATE DIMENSION TABLE [schema].onekusers (

     user_id varchar,

     artist_id varchar,

     artist varchar,

     plays bigint

         )

    COMPRESS LOW;

Let’s also assume that you are using Aster Express in a Windows environment. In order to load the data, take the following steps:

  1. Download the Windows Aster Client .zip file that is appropriate for your system; this can be downloaded here: http://downloads.teradata.com/download/aster/aster-client-tools-for-windows.
  2. Drill down until you see the ncluster_loader.exe file. Copy this file to the file on your local system where your data is saved.
  3. Connect to your Aster Express queen node.
  4. Open the Windows Command prompt. In the prompt, change directories to the file where you have both your data and the ncluster_loader.exe file saved.
  5. Since we have input with special characters in this example, convert the input file to UTF-8 encoding before loading it into an Aster database. To do this, take the following steps:
    1. Open the file with Notepad++ or another text editor of your choice.
    2. Select “Encoding > Encode in UTF-8”.
    3. Save the file.
  6. Run the following code on the Windows Command prompt in order to load your converted data:

ncluster_loader.exe –h [queen node IP address] –U [username] –w [password] –d [database] –c --verbose --skip-rows 1 --el-enabled [schema].onekusers [Windows file path to where the converted file is saved. Include name of converted file (with extension) in this path.]

IMPORTANT: Make sure that your Windows file path does not contain any spaces, or you will get an error when you try to run the code above.

Your data is now in a Teradata Aster database, inside the table that you created with SQL.

Now, we run our data through Teradata Aster’s cFilter function in order to get our artist pairings:

     SELECT * FROM CFilter (

          ON (SELECT 1)

          PARTITION BY 1

          INPUTTABLE('[schema].onekusers')

          OUTPUTTABLE('[schema].onekuserscfilter')

          INPUTCOLUMNS('artist')

          JOINCOLUMNS('user_id')

          DROPTABLE('true')

     );

     SELECT * FROM [schema].onekuserscfilter;

Our results look like this:

cFilter output table.PNG

Figure 3. Sample of output table from cFilter function

Now, we can log in to AppCenter and start building our app. After entering the required information on the App Info tab and checking the boxes for “UI” and “Portable,” we go to the Input Tables tab. On the Input Tables tab, we select [schema].onekuserscfilter and import its columns. This tab should then look like this (there will also be columns for confidence, lift, and z_score):

Dialog Box - input tables cfilter output1.PNG

Figure 4. Input Tables tab – cFilter output table with columns imported

Next, we go to the Input Parameters tab and fill it out as shown in Figure 5:

Dialog Box - Input Parameters tab - cfilter output.PNG

Figure 5. Input Parameters tab – Creating “artist” parameter with type: Column Values Selector

Next, we go to the Logic tab and enter the following code:

     --Create input table

     DROP TABLE IF EXISTS artists;

     CREATE TABLE artists AS(

          SELECT * FROM [schema].onekuserscfilter

          WHERE col1_item1 ILIKE '${artist}' OR col1_item2 ILIKE '${artist}'

          ORDER BY cntb DESC LIMIT 100

     );

     --Visualization code

     INSERT INTO app_center_visualizations  (json)

          SELECT json FROM Visualizer (

               ON "artists" PARTITION BY 1

               AsterFunction('cFilter')

               Title('Last.fm fans of "center" artist also listened to...')

               VizType('sigma')

               VizOptions('aggregation=ordered')

     );

We now click “Save” and go to our Run App screen, which should now look like this after we configure the app:

App with UI - includes drop-down menu.PNG

Figure 6. Run app screen – Drop down menu referencing a column of the input table

As you can see, our end user can now type "bey" in the parameter field and select "beyoncé" as an input choice without having to know how to generate the "é" with their keyboard! (In case you're wondering and using a Dell Latitude E5450 like me, hold the Fn and ALT keys, type "mkll" and release the Fn and ALT keys).

Here is the resulting sigma chart:

Sigma graph - top 50 artist combos with Beyoncé.PNG

Figure 7. Last.fm fans of Beyoncé also listen to these artists


Based on these results, the top three artists that Beyoncé fans also listen to are Rihanna, Britney Spears, and Lady Gaga. (Red paths indicate the strongest affinities, while yellow paths indicate the weakest affinities).


Method #2. Creating Drop-Down Menu Choices Manually

Another way to create a drop-down menu in an AppCenter app UI is to build the menu by hand (instead of including every single value from a column in the input table). This is useful when you only have a few choices that you want your end user to be able to pick from. To do this, follow the same steps as you did with Method #1, but with these differences:

  1. Under the Input Parameters tab, select Type: Menu
  2. Under Additional Settings, click “Add Item”. Your dialog box will now look like this:

Dialog Box - Input Parameters type Menu.PNG

Figure 8. Input Parameters tab – Additional settings for type: Menu


   3.  The “List item label” and “Value” fields are where you will set up the choices for your drop-down menu. “List item label” is where you enter the item name as you want it to appear in the drop-down menu, while “Value” is the item value as it appears in the input data set.

   4.  Keep adding items until your list is complete.

Continuing our example, suppose that we are now only interested in producing sigma charts for four music artists of interest. We go back and edit our Input Parameters tab so that it looks like this:

Dialog Box - Input Parameters tab - manually created drop down menu.PNG

Figure 9. Input Parameters tab – Creating “artist” parameter with type: Menu


Your run app screen will now look like this:
Run App Screen Drop-Down Menu Type - Menu.PNG
Figure 10. Run app screen - Manually created drop-down menu

As you can see, our drop-down menu is now limited to four items. End users can click the drop-down arrow, select any one of these items, and run the app. Also, they can still type in the parameter field, if desired.

Note: When you are hooking up the app to an existing input table, and you are not changing the input table, the items in the “Value” fields on the Input Parameters tab must be legitimate values from the input table. If you include values that do not exist in the input table, such as the fictitious band “The Dusty Lampshades,” the app will run without errors, but no visualization will be produced.

However, if you are using an AppCenter app to generate a table or to add rows to an existing table, then it is okay to set new values in the “Values” fields for your list items.

In conclusion, we have now learned two ways to create drop-down menus in user interfaces for AppCenter apps. Drop-down menus are useful for parameters where you want to limit user input to specific values. They can help to avoid misspellings, typos, and invalid input that would cause an app to produce inaccurate results. This, in turn, can help save your end user from a lot of frustration and heartache, and it can help keep your users happy.