Learning module: Data Load Basics

Example Download
Exercise - INSTRUCTIONS The example files provided for
this exercise are of several
Analysis Goal: Use several types of simple data files to explore the basic different types.
features involved in the process of loading data into a Qlik Sense Desktop app.
All of the example files are
provided within the zip archive,
available for download from the
course site. Extract the files
Create a new app
from the zip archive and place
• Launch Qlik Sense. them in a convenient location in
your Windows directory.
• Create a new app.
o Name of my new app: “Data Load Exercise”. The scenario for this exercise
involves the analysis of sales
made in the month of March,
Load the first table 2015. We sell to companies who
• Use the Add data From files and other sources option to load data, with are organized into different
regions. Our sales department
the goal of adding Excel data to the app.
assigns a salesperson to each
• Navigate to the location of the example files you downloaded for this region, and each salesperson is
responsible for two or more
module.
regions.
• Select the file titled: MarchSalesData.xlsx.
Here are the files used in this
• Include all three fields from this Excel spreadsheet, and click the Next
exercise assignment:
button.
MarchSalesData.xlsx
Contains company name, date
Review data in the Data model viewer and sales amount
• Navigate to the Data model viewer ( ), and open it in a new tab, in CompanyRegion.xml
order to become familiar with the underlying data. Contains company name and
region company is located

RegionalSalesperson.txt
Add a second table Contains salesperson name
and regions they are assigned
• Navigate to the Data manager view ( ), and open it in a new tab.
• Use the + Add data > Files and other sources option to add a second
table to the app.
• Navigate to the location of the example files and select the file: CompanyRegion.xml.
• Include both fields from the table in the XML file.

• Switch the Data profiling option to Disabled (in order to automatically associate tables
with common field names).

• Click the Next button to complete the data load process.

© 2024 QlikTech International AB. All rights reserved. All company and/or product names may be trade names, trademarks and/or registered trademarks of the respective
owners with which they are associated. For the full list of Qlik trademarks please visit: https://www.qlik.com/us/legal/trademarks..
Review data in the Data model viewer (again)
• Return to the tab showing the Data model viewer in order to evaluate the association between these
two tables.
o Which field was automatically identified as the key field? (Answer = Company)

Review associations in the Data manager view

• Return to the tab showing the Data manager view.
• Note that the bubbles are connected with a link to represent the association between these two tables.
o Click on the link, and examine the preview panel to confirm that the Company field in each
table was used to form the association.

Create a visualization based on associated tables

• Return to the tab showing the sheet view, and toggle the sheet into ‘edit’ mode.

o If present, ensure that the Advanced options switch is toggled to the ON position.

• Click the Charts ( ) icon in the assets panel, and add a Bar chart to the sheet, configured with the
following dimension and measure:
o Dimension = Region
o Measure = Sum(Sales)
(NOTE: These fields originate in separate tables.)

Add a third table

• Use the Fields ( ) section of the assets panel to add a third table to the app. Click the + Add data
> Files and other sources button at the bottom of this section.
• Navigate to the location of the example files and add the file: RegionalSalesperson.txt.

• Ensure that the Field names: ‘SalesRegion’ and ‘Salesperson’ are correctly interpreted in the select
data dialog. [Hint: Ensure that the Field names selector is set to Embedded field names.]

• Leave the Data profiling switch set to Enabled, and click on the Next button.

• Click the Load data button. (Note that the tables should remain unassociated, for now).

• Navigate to the App overview ( ), opened in the current tab, and click to open My new sheet.

Review data in the Data model viewer (yes, again)

• Return to the tab showing the Data model viewer.
• Rearrange tables as needed to see the lines, representing associations, between tables.
• Note the lack of an association between the RegionalSalesperson table and the rest of the data model.

© 2024 QlikTech International AB. All rights reserved. All company and/or product names may be trade names, trademarks and/or registered trademarks of the respective
owners with which they are associated. For the full list of Qlik trademarks please visit: https://www.qlik.com/us/legal/trademarks. page 2
Create a visualization based on unassociated tables
• Return to the tab showing the sheet view, toggle into ‘edit’ mode, and use the Charts section of the
assets panel to create a new bar chart which displays the following data:
o Dimension = Salesperson
o Measure = Sum(Sales)
(NOTE: The fact that all bars in this chart are the same height is evidence that the fields selected are not
supported by the associations in the underlying data model. Consider that the associations required to
relate Salesperson to Sales values are missing.)

Correct the association issue

• Return to the tab showing the Data manager view in an effort to correct the association problem.

• In the Recommended associations panel, click on the Region-SalesRegion recommendation.

o A colored link appears between the RegionalSalesperson table and the

records/records table, and the preview panel shows that the Region and
SalesRegion fields contain matching values which are used to form this association.

o Click the Apply button.

• Click the Load data button in order to complete the process of forming this new association between
tables.

Review data in the Data model viewer (I promise, this is the last time)
• Return to the tab showing the Data model viewer.

• Rearrange tables, as required, to see the lines (representing associations) between tables.
o What is the association path between Salesperson and Sales fields?
(Answer = Salesperson is related to Region-SalesRegion; Region-SalesRegion is associated
with Company; Company is associated with Sales)

© 2024 QlikTech International AB. All rights reserved. All company and/or product names may be trade names, trademarks and/or registered trademarks of the respective
owners with which they are associated. For the full list of Qlik trademarks please visit: https://www.qlik.com/us/legal/trademarks. page 3
Review visualization
• Return to the sheet where you configured the invalid bar chart in Step 10.
• Based on the fact that there is now a valid association path, the bar chart showing Sum(Sales) per
Salesperson is now valid.

• However, the original bar chart you configured may now indicate an Invalid dimension. This is because
the new association has caused the Region field name to change to Region-SalesRegion.

• Correct this visualization as follows:

o Delete the Invalid dimension.
o Add the dimension:
Region-SalesRegion

© 2024 QlikTech International AB. All rights reserved. All company and/or product names may be trade names, trademarks and/or registered trademarks of the respective
owners with which they are associated. For the full list of Qlik trademarks please visit: https://www.qlik.com/us/legal/trademarks. page 4