Skip to main content

Selecting and loading data

Loading data from files, such as Microsoft Excel or any other supported file format, is easily done by using the data selection dialog in the data load editor.

Do the following:

  1. Open Qlik Sense.
  2. Create a new app.
  3. Name the app Scripting Tutorial, and then click Create.
  4. Open the app.
  5. Information noteBefore you load data into your app for the first time, there is an option to use Add data to easily load data from files. However, in this tutorial we want to see the script, so we will use the data load editor.
  6. Open the data load editor from the drop-down menu in the top toolbar.
  7. Click P in the left menu to add a new script section below the section named Main.
  8. Using more than one section makes it easy to keep your script organized. The script section will execute in order when you load data.

  9. Give the section a name by typing Sales.
  10. New Sales tab in Scripting Tutorial app
    New Sales tab in Scripting Tutorial app.

  11. Under DataFiles in the right menu, click Select data.

  12. Select data window
    Select data window.

  13. Upload and then select Sales.xlsx. A data preview window opens.
  14. Data preview window for Sales data file
    Data preview window for Sales data file.

  15. Deselect the fields # of Days Late and # of Days to Ship. You might need to click on the field headings to see the complete field names.
  16. Search for date in the Filter fields search field.
  17. Click on the heading Invoice Date and type Bill Date to rename the field.
  18. Click Insert script. The load script is inserted into the Sales section of the script editor. Note that Qlik Sense puts double quotes around field names that contain a space.
  19. Your script should look like this:

    Load script in Sales tab
    Load script in Sales tab.

  20. Add the following row above the LOAD statement to name the table Table1:
  21. Table1:

  22. Now adjust the script to ensure that the dates are interpreted correctly. Change the Date field to the following:
  23. Date#(`Date`,'MM/DD/YYYY') as "Date",

    Your script should look like this:

    Updated load script in Sales tab
    Updated load script in Sales tab.

  24. In the upper right corner, click Load data.
  25. This will load the data into the app. A script execution progress window is displayed. When it is finished you will see a summary of possible errors and synthetic keys even if there are none.

  26. Click Close.
  27. Open the data model viewer from the drop-down menu in the top toolbar. By clicking w the data model viewer will open in a new tab.
  28. Select s and + in the top menu to show the table view that is used in this tutorial. If your table is not displayed properly, you can remove the existing load script and build the script again.
  29. Table view in data model viewer of Sales data
    Table view in data model viewer of Sales data.

    Now, let's load another table called Dates. After we load the table, Qlik Sense will connect it with the Sales table on the Date field.

  30. Open the Data load editor.
  31. Click P to add a new script section.
  32. Name the section Dates. If the new section Dates is not already placed below Sales, put the cursor on the o drag bars, and then and drag the section down below the section Sales to rearrange the order.
  33. Click on the top row of the script and click .
  34. Make sure // is added into the script.

  35. Add the following text after //:
  36. Loading data from Dates.xlsx

    The top line of your script should now look like this:

    // Loading data from Dates.xlsx

  37. Under DataFiles in the right menu, click Select data.

  38. Tip noteUnder Field names, make sure that Embedded field names is selected to include the names of the table fields when you load the data.
  1. Upload and then select Dates.xlsx. A data preview window opens.
  2. Data preview window for Dates data file
    Data preview window for Dates data file.

  3. Click Insert script.
  4. Your script should look like this:

    Load script in Dates tab
    Load script in Dates tab.

  5. Add the following on the row above the LOAD statement to name the table Table2:
  6. Table2:

  7. To ensure that the Month column in the file Dates.xlsx is interpreted correctly in Qlik Sense we need to apply the Month function to the Date field.
  8. Change the Date field to the following:

    Month (Date) as "Month",

    Your script should look like this:

    Updated load script in Dates tab
    Updated load script in Dates tab.

    Now you have created a script to load the selected data from the file Dates.xlsx. It is time to load the data into the app.

  9. In the upper right corner, click Load data.
  10. When you click Load data, the data is loaded into the app and the script is saved.

  11. When the script execution is finished, click Close.
  12. Open the Data model viewer.
  13. Now you can see that a connection has been made between the two fields named Date in the two tables.

    Table view in data model viewer
    Table view in data model viewer. Tables are connected on Date field.

  14. Click Preview in the bottom left corner. Click on the name of the table Table2.
  15. This will display information about the table. In the Preview field you can see that 628 rows of data have been loaded into the internal table Table2. If you instead click on a field in the table, you will see information about the field.

    Table preview in data model viewer
    Table preview in data model viewer.

    The data is now available to use in visualizations in an app. We will show you how later in this tutorial.