Table Recipe

[Jennell_Yorkman]

A table recipe is an easy, no-code way to prepare a data set for an app, data flow, script or ML experiment. It is a great way for new users to prepare their data and get started building an app without having to write the script to transform the data. It can also be used by experienced users for quick data prep. Table recipe is available in Qlik Cloud Analytics from the Analytics activity center > Prepare data. You can also access it from the + Create menu option or + Create New button in the catalog. To begin using it, open table recipe, give it a name and select the space you would like to create it in. Once that is done, you will be prompted to select the data set. Note - the table recipe processes one data set at a time.

Once a data file is selected, you can view the fields to confirm it is the correct data set and then you can load it into table recipe. Once it is loaded, you will see a pop up indicating the number of rows and columns in the dataset. Note that all the rows may not be loaded into the table recipe editor, but the recipe will be applied to the entire data set when it is run. Below is a look at the table recipe. On the left is the functions panel that includes several functions that can be used to prepare the data. Functions are grouped as general, columns, strings, dates, numbers and math. On the right is where the steps in our recipe will be added.

Let’s begin with a simple step like deleting a column. I have a Fax column that I do not need so to delete the column; I can click on the column menu (three vertical dots) and select Delete column.

The step will appear on the right in the table recipe, and I can click Apply to remove the Fax column.

Next, let’s split a column into two new columns. I have a Contact Name field with the contact’s full name. I would like to split the name into two fields – one for first name and one for last name. To do this, I will select the Split column function from the list of string functions on the left. Then I can update the details and apply the step. I will change the separator to a space since that is what separates the first and last name in the ContactName column.

Once I apply that step, I can see the two new fields it generated.

I will open the column menu and select Rename column to change the names of the new columns. Once I apply those steps, the columns and the recipe look like the image below. Keep in mind that the table recipe has steps that flow in a specific order. If those steps were to change, you may not get the expected results. For example, if I moved the rename column step above the split column step it would not work because the column I am renaming does not exist yet.

It is also easy to reorder the columns with the table recipe. The Reorder column function is found in the function group named Columns. Simply, select the column you would like to move and click on the Reorder column function. To move the Address column to the left of the City column, I will select the City column from the Anchor column drop down and then click Apply.

 

We are also able to apply filters to the data if we want to focus on specific rows. In this example, Country has two different values for the United States – USA and US. I would like to change the US values to USA.

After applying the filter, I can see that I have 6 rows with US as the Country. I can use the string Search and replace function to replace US with USA.

This step included my filter, so it only applied it to the rows I have filtered on.

Let’s perform one more step that I use all the time when loading data. We will extract date parts from the OrderDate field. This will allow us to create other date fields such as month and year fields. If I select the OrderDate field and then select the date function Extract date parts, I am presented with all the possible date part fields I can add as a column to my data set. I simply can toggle on any new fields I would like to add.

Once this step is applied, I can see the 4 new date fields I added.

When all the steps have been added to the table recipe, the next steps are to set the target and run the recipe. Setting the target is nothing more than giving the target file a name and file type (qvd, parquet, txt or csv) and selecting the space to create it in.

What is nice about the table recipe is the steps are applied and stored in a new file, separate from the original data set, so you do not have to worry about incorrectly modifying the data set. Once the target is set, the Incomplete recipe message at the top of the page will change to Valid recipe and the Run recipe button will be enabled. Once the recipe is run, you can view your new data set and use it accordingly. The target data set will be stored in the desired space and can be accessed anytime via the catalog.

Notice how all these steps we are creating are very easy and intuitive to build. You do not have to be an expert to perform these steps, and the editor is very user friendly and has a clean flow. What is also nice is that you can see that each step is working as expected when you apply it. Take this product tour to get a feel for the table recipe and then try it for yourself. To learn more, check out Qlik Help.

Thanks,

Jennell