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.
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.
5. Open Data load editor from the drop-down menu in the top toolbar.

6. Click in the left menu to add a new script section below the section named Main.

Using more than one section makes it easy to keep your script organized. The script section will execute in order when you load data.

7. Give the section a name by typing Sales.

New Sales tab in Scripting Tutorial app

8. Expand the Data sources panel if not already expanded. A menu appears on the right.

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



10. Upload and then select Sales.xlsx. A data preview window opens.



11. 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.
12. Search for date in the Filter fields search field.
13. Click on the heading Invoice Date and type Bill Date to rename the field.
14. 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.

Your script should look like this:



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

Table1:

16. Now adjust the script to ensure that the dates are interpreted correctly. Change the Date field to the following:

Date#(`Date`,'MM/DD/YYYY') as "Date",

Your script should look like this:



17. In the upper right corner, click Load data.

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.

18. Click Close.
19. Open the data model viewer from the drop-down menu in the top toolbar. By clicking the data model viewer will open in a new tab.
20. Select 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.



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.

21. Open the Data load editor.
22. Click to add a new script section.
23. Name the section Dates. If the new section Dates is not already placed below Sales, move the pointer over , and then drag the section down below the section Sales to rearrange the order.

24. Click on the top row of the script and click .

    Make sure // is added into the script.

25. Add the following text after //:

Loading data from Dates.xlsx

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

// Loading data from Dates.xlsx

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

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.
27. Upload and then select Dates.xlsx. A data preview window opens.



28. Click Insert script.

Your script should look like this:



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

Table2:

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

Change the Month field to the following:

Month (Date) as "Month",

Your script should look like this:



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.

31. In the upper right corner, click Load data.

When you click Load data, the data is loaded into the app and the script is saved.

32. When the script execution is finished, click Close.
33. Open the Data model viewer.

Now you can see that a connection has been made between the two fields named Date in the two tables.



34. Click Preview in the bottom left corner. Click on the name of the table Table2.

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.



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