Learning module: Data Load Basics

Exercise - INSTRUCTIONS Example Download

The example files provided for
Analysis Goal: Use several types of simple data files to explore the basic this exercise are of several
features involved in the process of loading data into a Qlik Sense Desktop app. different types.

All of the example files are

provided within the zip archive,
available for download from the
Step 1: Create a new app
course site. Extract the files
• Launch Qlik Sense Desktop. from the zip archive and place
them in a convenient location in
• Create a new app. your Windows directory.
o Name of my new app: “Data Load Exercise”.
The scenario for this exercise
involves the analysis of sales
Step 2: Load the first table made in the month of March,
2015. We sell to companies who
• Use the Add data from files and other sources option to load data,
are organized into different
with the goal of adding Excel data to the app. regions. Our sales department
assigns a salesperson to each
• Click on My computer, and navigate to the location of the
region, and each salesperson is
example files you downloaded for this module. responsible for two or more
regions.
• Select the file titled: [Link].

• Include all three fields from this Excel spreadsheet, and click the Add Here are the files used in this
data button. exercise assignment:

[Link]
Step 3: Review data in the Data model viewer Contains company name, date
and sales amount
• Navigate to the Data model viewer ( ), and open it in a new tab, in
order to become familiar with the underlying data. [Link]
Contains company name and
• Expand the table to show all fields ( ). region company is located

[Link]
Step 4: 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 option on the global menu to add a second
table to the app.

• Click on My computer and navigate to the location of the example files.

• Select the file: [Link].
• 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 Add data button to complete the data load process.

© 2018 QlikTech International AB. All rights reserved. Qlik®, Qlik Sense®, QlikView®, QlikTech®, Qlik Cloud®, Qlik DataMarket®, Qlik Analytics Platform®, Qlik NPrinting®, Qlik
Connectors®, Qlik GeoAnalytics®, Qlik Core™, Associative Difference™ and the QlikTech logos are trademarks of QlikTech International AB which have been registered in multiple
countries. Other marks and logos mentioned herein are trademarks or registered trademarks of their respective owners.
Step 5: 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)

Step 6: 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.

Step 7: Create a visualization based on associated tables

• Return to the tab showing the sheet view.
• 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.)

Step 8: Add a third table

• Use the Fields ( ) section of the assets panel to add a third table to the app. Click the + Add button
at the bottom of this section.

• Click on My computer and navigate to the location of the example files.

• Add the file: [Link].

• 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 use the Add data button.

• Click the Load data button.

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

Step 9: 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.

© 2018 QlikTech International AB. All rights reserved. Qlik®, Qlik Sense®, QlikView®, QlikTech®, Qlik Cloud®, Qlik DataMarket®, Qlik Analytics Platform®, Qlik NPrinting®, Qlik
Connectors®, Qlik GeoAnalytics®, Qlik Core™, Associative Difference™ and the QlikTech logos are trademarks of QlikTech International AB which have been registered in multiple
countries. Other marks and logos mentioned herein are trademarks or registered trademarks of their respective owners.
Exercise Instructions | page 2
Step 10: Create a visualization based on unassociated tables
• Return to the tab showing the sheet view, and 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.)

Step 11: 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 blue-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.

Step 12: 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)

• Save the app.

© 2018 QlikTech International AB. All rights reserved. Qlik®, Qlik Sense®, QlikView®, QlikTech®, Qlik Cloud®, Qlik DataMarket®, Qlik Analytics Platform®, Qlik NPrinting®, Qlik
Connectors®, Qlik GeoAnalytics®, Qlik Core™, Associative Difference™ and the QlikTech logos are trademarks of QlikTech International AB which have been registered in multiple
countries. Other marks and logos mentioned herein are trademarks or registered trademarks of their respective owners.
Exercise Instructions | page 3
Step 13: 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

© 2018 QlikTech International AB. All rights reserved. Qlik®, Qlik Sense®, QlikView®, QlikTech®, Qlik Cloud®, Qlik DataMarket®, Qlik Analytics Platform®, Qlik NPrinting®, Qlik
Connectors®, Qlik GeoAnalytics®, Qlik Core™, Associative Difference™ and the QlikTech logos are trademarks of QlikTech International AB which have been registered in multiple
countries. Other marks and logos mentioned herein are trademarks or registered trademarks of their respective owners.
Exercise Instructions | page 4