Working with table recipes
Creating a table recipe
To create a new table recipe:
-
From the launcher menu, select Analytics > Create or Analytics > Prepare data.
-
Click Table recipe.
The Create a new table recipe dialog opens.
-
In the corresponding field, enter a Name for your table recipe.
-
From the corresponding drop-down list, select in which Space you want to save the table recipe.
-
Add a Description to document the purpose of the table recipe.
-
Add some Tags to the table recipe to make it easier to find.
-
Optionally select the Open table recipe checkbox to directly view the table recipe once it’s created.
-
Click Create.
Your new table recipe opens in the Editor tab and you are prompted to select a source from your catalog. See Selecting a data source for more information on how to proceed.
Selecting a data source
Adding a source right after the table recipe creation
Right after Clicking Create in the table recipe creation dialog, a Data catalog window opens. To use as source for your table recipe, you can select any of your catalog datasets that are based on files (.qvd, .xls, .csv, .parquet, .json, etc.).
To select a dataset as source for your table recipe:
-
Browse for previously uploaded datasets, or click Upload data file to browse for files on your computer and upload them on the fly.
-
Using the search and filters, select the checkbox in front of a single dataset from your list and click Next.
-
In the Summary tab, you can review the dataset you have selected, check the fields it contains, and exclude some if you want. Click Load into table recipe.
When the load is successful, a summary opens detailing how many rows and columns will be displayed in the sample table. After you close the summary, you can see the loaded data in the editor in the form of a table with multiple columns corresponding to the selected fields.
Adding a source some time after the table recipe creation
If a table recipe was created without setting a source by closing the Data catalog window during the process, you can still set a source the next time you open it.
-
Open the table recipe without source.
-
Click one of the two Set source buttons:
-
In the middle of the empty table area.
-
In the source section of the Table recipe panel.
the Data catalog window opens.
-
-
Browse for previously uploaded datasets, or click Upload data file to browse for files on your computer and upload them on the fly.
-
Using the search and filters, select the checkbox in front of a single dataset from your list and click Next.
-
In the Summary tab, you can review the dataset you have selected, check the fields it contains, and exclude some if you want. Click Load into table recipe.
When the load is successful, a summary opens detailing how many rows and columns will be displayed in the sample table. After you close the summary, you can see the loaded data in the editor in the form of a table with multiple columns corresponding to the selected fields.
Changing the source of the table recipe
In the case of an existing table recipe with functions already applied on the table, it is still possible to select different fields to load, or another source for your table recipe altogether.
-
In the source section of the Table recipe panel, click the current source to expand the configuration menu.
-
Click Change source.
-
Click Change source again in the confirmation modal that opens.
-
Browse for the new dataset to use in the Data catalog window, like you would do for a new table recipe.
After loading the new dataset or new fields in the table recipe, the source changes and the data in the table is updated, but the recipe steps are kept. If the schema is not the same as before, the recipe will become invalid and you will need to reconfigure your recipe steps. See Editing a function for more information.
Configure the file settings of CSV and Excel sources
If you are using a csv or excel file dataset as source, either previously uploaded to your catalog or directly uploaded during the process, and the data does not display properly in the table, it could mean that the file is not properly formatted.
Possible issues include a wrong header size or wrong separator configuration. To troubleshoot these issues, you will need to go to the dataset settings.
-
From the table recipe, point your mouse over the source section of the right panel and click the View dataset in a new tab button.
You can also open the dataset from your Catalog.
The dataset opens and in the overview, you can see a warning saying that there is a potential formatting error.
-
Click the link to the File format settings from the warning message or use the More actions menu on the top right of the overview.
-
Use the Delimiter dropdown list to select the expected delimiter, or enter a new value in the Header size field.
-
Click Save.
-
Back in your table recipe, click the current source in the right panel and select Change source. Select the same dataset as before from the catalog and reload the source. This time, the source will reflect the right dataset configuration.
Adding a function
Applying a function to a column
Functions corresponds to the different transformations you can apply on the data at the column level. Around fifty functions are available, split in different categories depending on the data type. For more information, see Table recipe functions.
To apply a function on a column:
-
Click anywhere in a column to highlight and select it.
-
In the Functions panel, browse the list of functions or use the search field to find the function to apply.
-
Click the function of your choice.
In the Table recipe panel, the function configuration menu opens.
-
Fill the different configuration parameters according to the desired outcome.
-
Click Apply.
The transformation is applied to the selected column and is listed in the recipe as an individual step.
Recipe steps can be modified later if needed. See Editing a function for more information.
Applying a function to multiple columns
Rather than applying the same function to different columns one after the other, you can perform actions on several columns at the same time.
To apply a function to multiple columns, you can:
-
Select multiple columns at once before clicking a function, using Ctrl + click or Maj + click.
-
Start applying a function to a single column and then change the Columns to process in the function configuration.
Configure the function and click Apply.
Editing a function
Any function previously applied can be edited from the recipe. But because each recipe step is based on the previous one, any modification on a step can cause errors to the following ones. Make sure your recipe steps configuration do not reference deleted or renamed columns for example.
To edit a previously applied function:
-
In the recipe section of the Table recipe panel, click the step to edit.
The step expands and the current configuration is now visible.
-
Directly edit any configuration parameter you want to change.
-
Click Apply.
The table is refreshed with the new effects of the function now active.
If a step is not necessary anymore, you can simply remove it using the trash icon.
Filtering data
In order to have a more specific idea of the data contained in your table or in order to perform functions on a certain subset of data, you can create up to five filters on your data.
Creating a quick filter
If you only need to filter similar values in a column, you can create a quick filter without going in the dedicated filter menu.
-
Right-click a specific value in a column.
-
From the quick actions menu that opens, click the Filter rows with this value option.
-
The filter is directly created, and the column now only displays the rows with the matching value.
Creating a complex filter
Complex filters allow you to combine more search criteria.
-
To start creating a filter, you can:
-
Click the Filter button in the table header.
-
Click the column menu of a column and select Filter column.
Information noteFor any given column, you can also use the column menu to directly filter rows with empty or null values. -
-
In the filter window that opens, create a filter by using the drop-down lists to select a column, an operator, and a value.
By default, the column value correspond to the currently selected one, but you can change it.
-
Combine more filters by clicking the Add filter button.
When more than one filter is applied on the table, they combine using an AND operator.
Use the trash icon to remove individual filters if needed.
-
Click Apply.
You can see in the table header that the filters have been correctly applied and the table now only displays the data corresponding to these criteria.
Information noteIf no data matches your filters, the table will be empty.You can apply a function on this subset of data, and the filter will be visible in the recipe step.
-
In the table header, click the cross in an individual filter to remove it, or use the Clear all option to remove all the filters at once and display the whole sample again.
Selecting a target
To output the results of your table recipe, you need to set a target, a file that will be stored in your catalog in Qlik Cloud. The prepared data can be exported as a .qvd, .parquet, .txt, or .csv file.
To set the table recipe target:
-
In the target section of the Table recipe panel, click Set to expand the target configuration menu.
-
From the corresponding drop-down list, select in which Space you want to export the target.
-
In the corresponding field, enter a File name for your target.
Information noteYou can write in a specific folder of the desired space. If you have created a folder called folder_name in your personal space for example, use folder_name/table_recipe_output.qvd as file name for your target. The resulting file will be directly sent to your folder. -
From the corresponding drop-down, select the file Extension to use for your target.
-
Click Apply.
Running a table recipe
Once all the steps of your recipe are correctly configured, a green check mark shows that the table recipe is considered valid and can be run. When running a recipe, the functions that you have used on the sample data will be applied to the entire source data.
-
Click Run recipe to start processing the data.
A notification opens to show the status of the run.
-
When the recipe has successfully completed, the prepared data that has been output can be found at different places:
-
In your Catalog among your other assets
-
In the Outputs section of the table recipe Overview.
-
You can now use this prepared data as clean source to feed an ML experiment, or in a visualization application.