qlikview 11 tutorial for developers – video...
TRANSCRIPT
QlikView 11 tutorial for developers – video scripts
The scripts for every video in the series
November, 2014
qlik.com
Table of Contents
Overview 3Data from websites: clean data 6Data from websites: loading partial data 9Data from websites: data cleansing 13Data from websites: spreadsheets 16Data from spreadsheets: simple data 19Data from spreadsheets: crosstables 22Displaying data: gauges and population pyramid (bar charts) 26Displaying data: displaying key parameters 31Displaying data: extension objects 33Displaying data: menus and filters 36Data from databases: getting and preparing databases 40Data from databases: configuring ODBC in Windows 42Data from databases: connecting to ODBC and loading data 44Data from databases: field names that change 47Data model: making new fields 50Data model: linking different tables – keys 52Data model: avoiding circular references 56Data model: TRACE: telling the user how a load is working 61Putting it all together 64
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 2
Overview
Topics covered An overview of the entire tutorial and a high level view of the completed application
Length (estimate) 4:04
Keywords QlikView tutorial
Time (mm:ss)
Shot Script
00:07 Slide:
QlikView 11 Tutorial for Developers
Welcome to this overview video for the QlikView 11 Tutorial for Developers. Over the next few minutes, I’m going to give you an overview of the tutorial. I’m going to tell you what we’ll cover, where the tutorial content is, and how to get the tutorial files.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
Application Bit by bit, we’re going to be building this application. It’s an application that shows vehicle fatalities in the US from 1975 onwards.
Slide The application reads in data from multiple different sources. It reads in ODBC data, Excel data, and data from tables on websites.
Slide The data comes from the US Census Bureau, the National Highway Traffic Safety Administration, the Bureau of Economic Analysis, and the Federal Highway Administration.
Application Let’s look at the application to see how it works.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 3
Time (mm:ss)
Shot Script
Application There’s an overview dashboard that shows fatalities over time and by State – we also get a map of which States have the worst fatalities per head of population.
Application We can look the population pyramid for the US…
Application …and make selections from a drop down menu.
Script In the script, we load in the different data sources,
rename fields,
create a link table to avoid circular references,
and use TRACE to show the progress of the data load.
Folder Here’s how the data files used by this application look on my disk.
Folder The database files are in the DBF folder. I have SQL Server Express running on my machine and that’s what I use as my database.
Folder The Census files are here
Folder The image files used in the drop down menu and elsewhere are here
Folder And the SVG files used to create the extension are here.
Tutorial web page Rather than build one huge application, we’ve broken the tutorial down into bite-sized chunks.
Tutorial web page You will learn how to build a population pyramid…
Tutorial web page …how to read in ODBC files…
Tutorial web page …how to read data from websites…
Tutorial web page …how to create a drop down menu and more
Tutorial web page Each step has a video and files. We’ve also made the script available too – you might find this helpful if your first language isn’t English, or you can’t stand the sound of my voice!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 4
Time (mm:ss)
Shot Script
Slide :
Next steps
Here’s what you can do next:
visit the web page view the other videos download the content
And, if you haven’t already done it, download QlikView
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 5
Data from websites: clean data
Topics covered Pulling data from a website and displaying it
Length (estimate) 3:06
Keywords QlikView data pull website
Time (mm:ss)
Shot Script
00:07 Slide: Data from web sites: clean data
This short video shows how you can use QlikView to extract data from websites and load it into a QlikView application for analysis.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:13 Web page:
BEA –personal income by State
This web page shows personal income per head of population for the year 2012 for the United States. It’s provided by the Bureau of Economic Analysis website.
How can we get this data into QlikView so we can analyze it?
00:07 QlikView
New document
Start QlikView and let’s go to the script editor – control-E. Our file is on the web, so let’s click Web Files.
00:07 QlikView
File Wizard: Source
It’s an internet file so let’s paste in the URL. We need to give the BEA website a second or two to respond.
00:12 QlikView QlikView has detected there are two tables on the page, let’s
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 6
Time (mm:ss)
Shot Script
File Wizard: Type choose the second one. This is the data we want.
Click Next. This takes us to the data transformation page which we’ll use in another video. For now, click Next again.
00:24 QlikView
File Wizard: Options
Here’s our data.
We don’t want FIPS, so click on the X so we don’t load it.
The column headed 2012 is actually the personal income data, so let’s rename it “Per capita personal income”.
Area is the State, so let’s rename that.
Clicking Next shows us the code that will be generated.
Click Finish
00:05 QlikView
Edit Script
This is our finished script. Let’s load the data – Control R.
00:06 QlikView Now we need to display the data.
Anywhere on the document right click and select New Sheet Object… Chart.
00:02 QlikView
General
Select bar chart
00:06 QlikView
Dimensions
The wizard wants to know the dimension. In this case, this is the quantity we want on the x axis. Let’s choose State.
00:08 QlikView
Edit Expression
The expression is the y axis in this case. We want the Per capita personal income.
00:03 QlikView
Sort
We want the highest income first.
00:05 QlikView
Axes
We want the x axis labels to be vertical and we want the axes to be drawn.
00:03 QlikView It’s money and it’s in $
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 7
Time (mm:ss)
Shot Script
Number
00:05 QlikView Let’s resize the chart.
And we’re done.
00:13 Slide
What we’ve done:
Pulled data from a website
Formatted it in the way we want
Plotted it out
We’ve done three things here.
We’ve pulled data from a website.
We’ve formatted it in the way we want.
We’ve plotted it out.
The total time to do this is under two minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 8
Data from websites: loading partial data
Topics covered Pulling partial data from a website and displaying it
Length (estimate) 5:40
Keywords QlikView some data website
Time (mm:ss)
Shot Script
00:07 Slide
Data from web sites: clean data
This short video shows how you can use QlikView to extract some of the data from websites and load it into a QlikView application for analysis.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:13 Web page
Vehicle miles traveled
FHA
Sometimes, the data on websites isn’t quite what we want. It might contain additional data items we don’t want, or it might even be badly formatted.
This page is a nice example. It’s a Federal Highway Administration web page that shows vehicle miles traveled by US State.
It contains data we want - State data, but also data we don’t want -data on regions, data totals, and data on Puerto Rico.
How can we only load the data we want?
00:07 QlikView
New document
Open QlikView and let’s go to the script editor – control-E. Our file is on the web, so let’s click Web Files.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 9
Time (mm:ss)
Shot Script
00:07 QlikView
File Wizard: Source
It’s an internet file so let’s paste in the URL. We need to give the Federal Highway Administration website a second or two to respond.
00:12 QlikView
File Wizard: Type
QlikView has detected there is one table on the page. Select it.
Looking at the display, this is the data we want.
Click Next. This takes us to a page asking us if we want to transform the data. We do. Click on ‘Enable Transformation Step’.
00:45 QlikView
File Wizard: Transform
There’s a lot we can do to transform the data.
Let’s start by removing the last three lines, US Total, Puerto Rico, and Grand Total. Click on rows 54, 55, and 56 and click Delete Marked.
<PAUSE 3>
We now need to do something about lines one and two. Columns 2 to 9 are rural road types and columns 10 to 17 are urban. For now, we’ll note this and delete row 1.
<PAUSE 3>
This gives us a table we can work with but the field names aren’t quite right. We’ll address that next. Click on Next.
00:25 QlikView
File Wizard: Options
We can edit the field names. Let’s change them to lower case and add the urban/rural distinction.
For the sake of time, I’ve speeded the video up a bit here.
<PAUSE 3>
Now click next.
00:05 QlikView
File Wizard: Script
The wizard now shows us the script it will generate. Click Finish.
00:25 QlikView
Script Editor
Here’s the script in the script editor. Everything looks OK, so let’s reload the data (Control R)– The data has now loaded – let’s look at it.
00:08 QlikView Let’s draw it as a bar chart. Right click somewhere in the sheet. Choose New Sheet Object and Chart.
00:08 QlikView Click on the bar chart icon, uncheck the box that says Show Title in
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 10
Time (mm:ss)
Shot Script
General
Chart and click Next.
00:08 QlikView
Dimensions
Click on State and Add– this will give us State on the x axis of the bar chart. Click Next.
00:30 QlikView
Expressions
We now want the y vales of the bar chart. We’ll plot Rural total, urban total, and total total. The y values are given as expressions and we’re now being prompted for the first expression.
Type in su and chose the Sum option from the menu. Now type in Rur and select Rural total.
Click on OK.
In the label box, type Rural total
00:25 QlikView
Expressions
We’ll now add the Urban total and total total. Click Add. Add Urban total. In the label box, type Urban total
Click Add again and add Total total. In the label box, type Total total.
Click Next, which takes us to the Style menu. Click Next to the Presentation menu, and Next to the Axes menu.
00:30 QlikView
Axes
On the Dimension axis (our x axis) let’s make the labels vertical.
Change the expression axis thickness to 1.
Click Next until we come to the Caption dialog. In the Title Text box, type “Vehicle miles traveled by State.”
Click Finish
00:05 QlikView Now, resize the chart.
<PAUSE 3>
00:20 QlikView We can interact with the chart to select areas of interest to zoom in on, or go back to our original graph.
<PAUSE 5>
There’s a lot more we can do to this chart, for example sorting by miles traveled, or changing the number format, or moving the key into the body of the chart. But we’ll leave that for another time.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 11
Time (mm:ss)
Shot Script
00:13 Slide
What we’ve done:
Pulled partial data from a website
Formatted it in the way we want
Plotted it out
We’ve done three things here.
We’ve pulled some of the data from a website – but not all of it.
We’ve formatted it in the way we want.
We’ve plotted it out.
The total time to do this is under five minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 12
Data from websites: data cleansing
Topics covered Pulling data from websites and cleaning it up
Length (estimate) 5:11
Keywords QlikView data cleansing
Time (mm:ss)
Shot Script
00:07 Slide
Data from web sites: clean data
This short video shows how you can use QlikView to extract some of the data from websites, clean it up, and load it into a QlikView application for analysis.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:13 Web page
State vehicle registrations
Sometimes, the data on websites isn’t quite what we want. It might contain bad data that we need to automatically clean up or data that we need to reformat. How can we clean up dirty data?
This is a Federal Highway Administration web page showing vehicle registrations by State.We have a list of states here, but note there are some odd extra characters. We’d also like to change Dist of Col to District of Columbia. We’ll show you how to do all this.
00:07 QikView Open a new QlikView Document and go to the script editor.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 13
Time (mm:ss)
Shot Script
00:07 QlikView
File Wizard: Source
It’s an internet file so let’s paste in the URL.
00:33 QlikView
File Wizard: Type
QlikView has detected there is one table on the page. Looking at the display, this is the data we want, but there are problems with extraneous characters. Looking at the web page, it’s coded as UTF-8, so we’ll change the character set to UTF-8. That doesn’t make the problem go away, but it does remove some characters.
Click Next. This takes us to a page asking us if we want to transform the data. We do. Click on ‘Enable Transformation Step’.
00:35 QlikView
File Wizard: Transform
Here, we’ll remove rows and columns we don’t want.
When we’re done, we’ll click on Next.
00:08 QlikView
File Wizard: Options
This looks like the data we want – but still with extraneous characters. We’ll click finish and clean up the data in the script. Load the data with control R.
00:30 QlikView Now let’s add a table to display the data.
00:06 QlikView The State names aren’t right – we need to change the loaded data to get rid of these extra characters.
00:20 QlikView
Script editor
What we want to do is replace the extraneous characters with nothing.
Let’s use the replace command to do it. Let’s replace the (1). Let’s reload and look at the results.
OK – this has replaced just one set of characters, how might we replace the (2) etc.?
00:25 QlikView
Script editor
We can call the replace function from within the replace function.
If you’ve programmed in C or other languages, this concept is familiar, it’s calling a function in the argument list of a function.
Let’s see how this works here. We want to replace the (1) string first, then look for the (2) string to replace.
Reload and look at the data.
00:05 QlikView We’ve replaced the (2) string with nulls.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 14
Time (mm:ss)
Shot Script
00:30 QlikView
Script editor
Now let’s replace the (3) and (4) strings and while we’re at it, replace the A character and change Dist of Col to District of Columbia.
Now reload the data.
00:25 QlikView As we can see, we’ve now removed all the extraneous characters and changed an abbreviation to something we want.
00:13 Slide
What we’ve done:
Pulled partial data from a website
Formatted it in the way we want
Plotted it out
We’ve done three things here.
We’ve pulled some of the data from a website – but not all of it.
We’ve formatted it in the way we want – renaming some things and removing extra characters.
We’ve displayed the data.
The total time to do this is under four minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 15
Data from websites: spreadsheets
Topics covered Pulling spreadsheet data from websites
Length (estimate) 4:31
Keywords QlikView website spreadsheet
Time (mm:ss)
Shot Script
00:07 Slide
Data from web sites: clean data
This short video shows how you can use QlikView to extract data from a spreadsheet on a website, clean it up, and load it into a QlikView application for analysis.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:13 Excel web page
2006 Vehicle Miles Traveled
Sometimes we want data from a website but it’s formatted as an Excel spreadsheet like this example here from the US Federal Highway Administration. The 2006 US vehicle miles traveled data is presented as a spreadsheet on this website. We’d like to extract the data without having to download it to disk.
Let’s see how we can do it.
00:07 QlikView
Script Editor
Open the QlikView Script Editor.
What we want is a web file, so let’s click on Web Files.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 16
Time (mm:ss)
Shot Script
00:07 QlikView
File Wizard: Source
It’s an Internet file, so let’s paste in the URL. Note the end of the URL, it’s vm2.xls, telling us we’re point to an Excel file on the web.
Click on Next.
00:33 QlikView
File Wizard: Type
This is the data we want, but we need to do some transformation work on it. Click on Next and click Enable Transformation Step.
00:35 QlikView
File Wizard: Transform
Let’s get rid of these rows. We’ll also remove some of the headings. Click Next.
00:30 QlikView
File Wizard: Options
The labels are embedded.
We’ll rename some of the fields.
Click on Finish and load the data (Control R).
00:30 QlikView Right click anywhere on the sheet and add a chart – specifically, a straight table.
Select STATE as the dimension. The expressions will be the urban, rural, and total totals.
00:30 QlikView
Script Editor
Note we have some extraneous characters we want to remove. Let’s go back to the script.
In a previous video on dirty data, we spoke about using the replace function to clean up data. We’ll use it again here.
Let’s remove the unwanted characters and change the name from Dist of Columbia to District of Columbia.
Let’s reload the data.
00:20 QlikView We’ve now cleaned up the data.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 17
Time (mm:ss)
Shot Script
00:13 Slide
What we’ve done:
Pulled spreadsheet data from a website
Formatted it in the way we want
Displayed it
We’ve done three things here.
We’ve pulled spreadsheet data from a website
We’ve formatted it in the way we want – renaming some things and removing extra characters.
We’ve displayed the data.
The total time to do this is under three minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 18
Data from spreadsheets: simple data
Topics covered Pulling data from a spreadsheet and displaying it
Length (estimate) 4:02
Keywords QlikView spreadsheet Excel
Time (mm:ss)
Shot Script
00:07 Slide: Data from spreadsheets: clean data
This short video shows how you can use QlikView to extract data from a spreadsheet and load it into a QlikView application for analysis.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:13 Website
US Census
This is the US Government’s Census Bureau website and we’re looking at a page that has population data from 1970-1979. Let’s use this as an example of how to read in data from a spreadsheet.
00:25 Website
US Census
Download the file PE-19.xls to disk. Let’s open the file and look at it. There’s a lot of data here and it’s not quite in the format we want. In the next few videos, we’ll be looking at how to get this data in the format we want. In this video, we’ll look at some basics.
00:12 QlikView Start a new QlikView document and go to the script editor (Control E)
In the script editor, click the table files button – after all, a spreadsheet is a table.
00:05 QlikView Find the Excel file and click open.QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 19
Time (mm:ss)
Shot Script
00:45 QlikView
File Wizard: Type
This doesn’t look quite right because the header is causing problems. Let’s get rid of it by changing the Header Size.
This looks better.
Now we need to tell QlikView that the labels are embedded. Select Embedded labels.
We don’t want the FIPS code, so click to remove it.
This still isn’t quite what we want. We’d like to split race and sex and we’d like the various age categories (e.g. Under 5 years, 5-10 years etc.) to be in one age category – but these are things we’ll tackle in later videos. For now, we’ll click finish.
Load the data by typing Control R.
00:10 QlikView Let’s look at the under 5 population by State. Right click anywhere on the sheet and select New Sheet Object and Chart.
00:06 QlikView
General
Uncheck the Show Title in Chart box and select the bar chart.
00:05 QlikView
Dimensions
Select State as the dimension.
00:15 Expressions Enter Sum([Under 5 years]) as the expression and set Under 5s as the Label.
00:25 QlikView
Sort
Sort by descending Y values.
Set the axes width to 1 and the x axis text to be vertical. Enable the x axis scrollbar.
Express the y value as a number with thousand separators.
Click finish.
00:13 QlikView Let’s resize the chart.
Now, we can move through the data using the scrollbar. If we want to see all of the data, or more items in the view, we can change the scroll bar settings.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 20
Time (mm:ss)
Shot Script
00:13 Slide
What we’ve done:
Pulled spreadsheet data from a website
Formatted it in the way we want
Displayed it
We’ve done two things here.
We’ve pulled data from a spreadsheet.
We’ve displayed the data.
The total time to do this is under two minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 21
Data from spreadsheets: crosstables
Topics covered Pulling data from a spreadsheet and using crosstables
Length (estimate) 6:18
Keywords QlikView spreadsheet Excel crosstables
Time (mm:ss)
Shot Script
00:07 Slide: Data from spreadsheets: crosstables
This short video shows how you can use QlikView to extract data from a spreadsheet and load it into a QlikView application for analysis when the spreadsheet data isn’t in the right format. The video demonstrates how QlikView can rearrange spreadsheet data using a crosstable.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 22
Time (mm:ss)
Shot Script
00:45 Excel spreadsheet Often we face a situation where a spreadsheet isn’t in the format we want. Databases work in terms of fields and rows, but spreadsheets don’t always.
Here’s a typical spreadsheet showing sales data. The data for each quarter is in its own column.
If we were to read it in in this format, we would have one dimension per quarter. That’s not what we want. We want one dimension to contain all these quarters. We want a quarters dimension.
One solution is to unravel the spreadsheet and reconfigure it like this.
But the problem is, this is tedious. Can we get QlikView to unravel it for us?
We can using something called a crosstable, and that’s the subject of this video.
Let’s move to some real data.
00:18 Website
US Census
This is the US Government’s Census website and we’re looking at a page that has population data from 1970-1979. Let’s use this as an example of how to read in cross table data from a spreadsheet. Download the file PE-19.xls to disk.
00:25 Website
US Census
Let’s open the file and look at it. There’s a lot of data here but it’s not quite in the format we want. We want the age range to be one dimension.
00:12 QlikView Start a new QlikView document and go to the script editor (Control E)
In the script editor, click the table files button – after all, a spreadsheet is a table.
00:05 QlikView Find the Excel file and click open.
00:45 QlikView
File Wizard: Type
This doesn’t look quite right because the header is causing problems. Let’s get rid of it by changing the Header Size.
This looks better.
Now we need to tell QlikView that the labels are embedded. Select Embedded labels.
At the transform dialog, click on Next.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 23
Time (mm:ss)
Shot Script
00:05 QlikView
File Wizard: Options
Now we will change the way the data is organized. Click on Crosstable.
00:45 QlikView
Crosstable
In the crosstable dialog we’re going to tell QlikView how to reorganize this data.
In Qualifier fields, increase the number to 4. These four fields – the ones in pink - will be unaffected by our reorganization. The remaining fields are our age range. In the attribute box, type the name we want the age ranges to appear in – we’ll type in age range.
The data field describes the numbers. What are these numbers? They are the number of people in that category. We’ll call that the Population.
Click OK.
We can now see the table has been re-arranged into a more helpful form. Click on finish.
00:10 QlikView
Script Editor
Note in the script editor the CrossTable prefix which is telling the load statement how to load the data.
Now load the data – Control R.
00:15 QlikView Let’s look at the age distribution for the population as a whole. Right click anywhere on the sheet and select New Sheet Object and Chart.
Uncheck the Show Title in Chart box and select the bar chart.
00:12 QlikView
Dimensions
Select Age range as the dimension. As the expression, type Sum(Population) and label it as population.
00:20 Sort the fields by load order.
Set the axes width to 1 and the x axis text to be vertical.
Express the y value as a number with thousand separators.
Click finish.
00:10 QlikView This gives us the population, but summed over all years and race/sex. Let’s add a list box to allow the year to be selected.
00:05 QlikView Right click anywhere on the sheet and select New Sheet Object and List box.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 24
Time (mm:ss)
Shot Script
00:05 QlikView
New List Box
Select Year of Estimate as the field. Click OK.
00:08 QlikView Step through the years looking at the effect on the population graph.
00:20 QlikView Now add a race/gender and a State name list box.
00:10 QlikView We can look at the population distribution by race/sex/state name/ or year.
00:13 Slide
What we’ve done:
Pulled data from a spreadsheet
Formatted it in the way we want
Displayed it
We’ve done three things here.
We’ve pulled data from a spreadsheet.
We’ve rearranged it in QlikView using a crosstable to better handle it.
We’ve displayed the data and provided a means of selecting various fields to look at.
The total time to do this is under three minutes.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 25
Displaying data: gauges and population pyramid (bar charts)
Topics covered Gauges and population pyramid
Length (estimate) 5:42
Keywords QlikView population pyramid and QlikView gauges
Time (mm:ss)
Shot Script
00:07 Slide: Making new fields
This short video shows how you can build a population pyramid in QlikView and build a gauge showing population gender balance.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:13 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:25 QlikView
Data Model
We’ll start with this QlikView application. It reads in US population data from a spreadsheet. The original data was sourced from the US Census Bureau.
Let’s look at the data model – control T. This shows us what fields have been loaded in and we can see a preview of the data. We have year, State, sex, age range, and population.
Let’s look at our application.
00:09 QlikView We can select year, State, and sex from the list boxes on the sheet, so all we need to do now is to display the population pyramid.
00:05 QlikView Let’s right click here and select New Sheet Object… followed by Chart.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 26
Time (mm:ss)
Shot Script
00:07 QlikView
General
Now let’s select bar chart, and while we’re at it, uncheck the Show Title in Chart box. Click next.
00:04 QlikView
Dimensions
We’ll select Age range as our dimension.
00:16 QlikView
Expressions
Type -Sum(Population), in the label type Male as the label.
Now add another expression, this time type in Sum(Population) and type in Female as the label. Click Next.
00:04 QlikView
Sort
Sort the age range by reversed load order. Click Next.
00:05 QlikView
Style
Change the orientation. Choose Stacked. Click Next.
00:05 QlikView
Presentation
Set the bar distance to be 0 and the cluster distance to be 0. Click Next.
00:05 QlikView
Axes
Set the axes width to be 1.
00:06 QlikView
Colors
Set the first color to be blue and the second color to be pink. Click Next.
00:05 QlikView
Number
Both male and female should be numbers.
00:07 QlikView
Caption
Set the caption to be US population 1975-2011.
00:30 QlikView The population pyramid is what we want, now let’s move the legend. Click on the chart and hold down Control-Shift. Now move the legend.
We can select year and State and look at the effect on the pyramid.
However, we have a problem, our pyramid isn’t right. The male and female sides of the pyramid are the same and are the total population, both male and female. We need to subset this data for male and female.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 27
Time (mm:ss)
Shot Script
Let’s go back to the chart and look at the expressions.
00:50 QlikView
Chart Properties
There’s a couple of ways we can solve this problem, but we’ll choose just one, we’ll use something called set analysis.
What we want to do is display the set of all males and the set of all females. We need to qualify our male and female expressions to do this.
In the female expression, type in Sum({$<Sex={'female'}>}Population)
The {} tells QlikView we are selecting a set, the $ says to use this set in conjunction with whatever the user has selected elsewhere (for example, year, State), the text in the angle brackets tells QlikView what set we’re working with – in this case all population data where the sex is female.
For the male population type in
-Sum({$<Sex={'male'}>}Population)
Click on Apply. We can see the population pyramid is now properly showing male and female.
00:12 QlikView This pyramid is nice, but we don’t get a good sense of the Sex balance in the population. Let’s use a gauge to give an indication of the Sex balance.
Right click, New Sheet Object… Chart.
00:19 QlikView
General
Uncheck show title in chart.
Select gauge.
There’s no dimension here, so click Next.
In the expression dialog, type:
Sum({$<Sex={'female'}>}Population) / Sum(Population)
This is the female population as a percentage of the whole population.
The label is % Female.
Click Next. Click Next on Sort.
00:04 QlikView
Style
On Style, click the top left style.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 28
Time (mm:ss)
Shot Script
00:45 QlikView
Presentation
We need to choose the segment colors. The first segment is 0-0.5, and we’ll use blue for that, and for 0.5 to 1, we’ll use pink.
In the box here, Text in Chart, click Add. Type in:
=num(100*(Sum({$<Sex={'female'}>}Population) / Sum(Population)), '##.#') & '%' We want to show the value as a percentage (multiply by 100 and use the % sign), but we only want one decimal place.
Show labels every 1 major unit.
Click on Next.
Click on Next for actions.
Click on Next for colors.
On Number, select Number and click on show in %. Click on Next.
On Font, set the font size to 12. Click on Next
On Layout, click on Next.
00:04 QlikView
Caption
In Caption, type in % Female in Title Text. Set Caption Alignment Horizontal to be centered. Click Finish.
00:07 QlikView Now let’s move the % female label. Control Shift and select the percentage – move it here.
QlikView We can now select State and Year and look at the population pyramid and gender balance and we can see how they change as we make different selections.
00:13 Slide
Population pyramid and Sex gauge
We’ve drawn a Population pyramid chart and a Sex gauge. We’ve seen how these charts respond to selections we’ve made in our application.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 29
Time (mm:ss)
Shot Script
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 30
Displaying data: displaying key parameters
Topics covered Displaying key parameters in text boxes
Length (estimate) 2:30
Keywords Displaying key parameter text QlikView
Time (mm:ss)
Shot Script
00:07 Slide:
Displaying key parameters as text
This video will show you how to display key parameters as text boxes.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:13 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:10 QlikView Here’s a US population application built from US census data. There’s a set of key parameters printed out at the top. Let’s right click the male population box.
00:20 QlikView
Text Object
Here’s how the Text object works. The text to be printed is here. In this case, we’re using set analysis to qualify the Population data – specifically, we’re choosing males. Sum means we’re summing across all dimensions. The num function specifies how the data is to be displayed, for example, where thousand separators are.
In the Caption tab, it specifies the position and size of the text box.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 31
Time (mm:ss)
Shot Script
00:12 QlikView
Line/Arrow properties
Right click on the grey line. This is a line/arrow we can create by right clicking on the QlikView application.
In the General tab, you can set the color.
In the Caption tab, you can set the position of the arrow.
00:07 QlikView Let’s add the total population in.
Right click and choose New Sheet Object… Text Object.
00:30 QlikView
New Text Object
In the text box, type =Sum(Population).
Set the background color to a pale grey.
Set the horizontal alignment to be left and vertical to be centered.
Set the text margin to be 0.
In Font, set the font to be 12.
In Caption, set the X position to be 10, the Y position to be 74, the width is 120, and the height 17.
Click on finish.
00:12 QlikView
Text Object Properties
This is almost what we want, but the format isn’t quite right. Let’s use the num function to reformat the data with the appropriate thousand separators.
00:05 QlikView We’ve now displayed key parameters as text in Qlikview.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 32
Displaying data: extension objects
Topics covered Displaying extension objects
Length (estimate) 3:48
Keywords QlikView extension objects
Time (mm:ss)
Shot Script
00:07 Slide:
What if the visualization you want isn’t available out-of-the-box in QlikView?
QlikView has a wide range of visualizations available out-of-the-box, but there will occasionally be cases where what you want isn’t available out-of-the-box. In these cases, extension object technology allows you to add your own custom visualizations. That’s the topic of this video.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 33
Time (mm:ss)
Shot Script
00:30 QlikView This application sources data from the Federal Highway Administration and shows vehicle miles traveled. The underlying data has Year, State, and road type fields as we can see from the data model (control T).
On this application, we display the vehicle miles traveled as a line chart that shows the variation over time. The table below shows State-by-State data.
It would be great to show a map of the US and indicate by colors the relative miles traveled. So, let’s do it!
00:17 Website
http://market.qlikview.com/qlikview-svg-map.html
There are plenty of tutorials and support materials on Extension Objects, so we’ll keep it simple here.
We want to display State-by-State data on a map, so we’ll use the Map available on the QlikMarket website.
Download the Map package and expand it.
00:15 Zip file contents Full instructions are in the Word file which you can read later.
Double click on the QAR file and you should see this dialog.
The SVG extension is now installed. We just need to use it. Let’s go back to our application.
00:10 QlikView Make sure you are in WebView. Now right click and choose New Sheet Object… Extension Objects.
Click and drag SVG Map onto the sheet.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 34
Time (mm:ss)
Shot Script
00:55 QlikView
SVG Reader
Right click on the SVG Reader and select properties.
Change the region ID to State abbreviation. This map is coded to accept two letter State abbreviations.
The measure is what we’re displaying, in this case we want Vehicle Miles Traveled for Rural roads in total.
The color expression is the color we want to use. In this case we’ll use the hex code for a blue (=’#71859D’ ) because we’d like our map to use just blue.
The popup contents are what we’d like to display when the user hovers his or her mouse over a State: ='<strong>' & [State abbreviation] & '</strong><br/>' & Sum([VMT Rural Total])
.
The map is of course the US.
Change to Caption and change the caption to Rural miles traveled.
We now have a color code map in action!
00:25 QlikView We want the same map, but for urban miles. Let’s copy the chart and change the settings.
The measure is now VMT urban total. The popup content changes too, to “='<strong>' & [State abbreviation] & '</strong><br/>' & Sum([VMT Urban Total])”.
We’ll change the color to 9d7185 – a shade of red
The Caption is now urban miles traveled.
00:10 QlikView As we hover our mouse over the States, the popup text we entered earlier comes up.
00:12 Slide
Maps
Extension objects
So we’ve now added a new visualization to our QlikView application using an extension object. We’ve configured our extension object to use our data, and we’ve displayed our new object.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 35
Time (mm:ss)
Shot Script
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 36
Displaying data: menus and filters
Topics covered Adding menus and filters to QlikView
Length (estimate) 5:24
Keywords QlikView menus and filters.
Time (mm:ss)
Shot Script
00:07 Slide:
Menus and filters
In QlikView, we can put up a list box and allow the user to select or filter dimensions they want to study. Wouldn’t it be nice if we could put that selection into a drop down menu?
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:33 QlikView Here’s a QlikView application that reads registered vehicle data from the US Federal Highway Administration website. The chart shows registered vehicles against time. As an aside, the chart shows how popular trucks have become in the United States over the last two decades.
At the bottom of the chart, we have list boxes where we can select the State or year we want to look at.
Unfortunately, these list boxes take up screen space which we could use to display data.
Can we move the list boxes into a drop down menu?
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 37
Time (mm:ss)
Shot Script
That’s what we’ll show now.
00:06 QlikView Right click on the application and choose New Sheet Object… New Text Object.
00:17 QlikView We want an image rather than text, so select Image, Change, and select Filters. Click OK. Move the box to the top right.
So far, the object we’ve just created does nothing. We need to add an action.
00:15 QlikView
Settings
Go to Settings, Variable Overview
Add a variable vShowFilter and give it a value of 0. This is the variable we’ll us to toggle the menu we’ll create on and off.
00:38 QlikView
Text Object Properties
Right click on the Text Object and go to the Actions tab. Add a new action – External – and select Set Variable. Click on OK.
The variable we are setting is vShowFilter. Here’s the action we want to take, =if(vShowFilter=0,1,0) - this has the effect of toggling the vShowFilter variable between 1 and 0.
Click on OK.
By clicking on the Filter button, we can toggle the variable. Now we need to do something with the variable we’re toggling.
00:05 QlikView Right click on the application and choose New Sheet Object…. Text Object.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 38
Time (mm:ss)
Shot Script
1:00 QlikView
New Text Object
Change the background to an image and select the image FilterBox.
We need to position the box, so go to the Caption tab and enter the Y-pos as 0 and the X-pos as 365.
We need this dialog to be on top of our other visual elements, like charts. Set the Layer to be Custom and set the value to be 0.
Add a new action – External – and select Set Variable. Click on OK.
The variable we are setting is vShowFilter. Here’s the action we want to take, =if(vShowFilter=0,1,0) - this has the effect of toggling the vShowFilter variable between 1 and 0.
Now, we need to make the dialog appear or disappear – we’ll do that using our vShowFilter variable.
On the Layout tab, Go to the Show area, click Conditional, and
add vShowFilter=1
Click on OK
1:00 QlikView Now, click on the Filter – a dialog appears. Click on the dialog, and it disappears.
So we’ve got the dialog, but no filters. Let’s add the filters. Click on the Filter to open the dialog.
Copy the State and Year list boxes onto the filter dialog.
This looks like what we want, but click on the dialog box. The dialog goes away, but the list boxes stay – not what we want.
Right click on State and choose properties. Go to Layout. For the Layer, select Custom and set the layer to be 1 – this ensures the list box appears on top of the dialog. Under show click Conditional and add vShowFilter=1 Click on OK.
Do the same actions for Year. Click on OK.
00:09 QlikView Now click on the Filter – the dialog appears – click on the dialog and it disappears.
Open the Filter dialog again.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 39
Time (mm:ss)
Shot Script
00:18 QlikView We can make selections in the dialog box and see the impact on the chart. We can now click in the dialog and the dialog disappears so we can see the results of our selection but without losing screen space.
00:09 Slide So we’ve created a dialog box, made it appear and disappear on mouse clicks, and added list boxes to our menu.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 40
Data from databases: getting and preparing databases
Topics covered Getting and preparing databases
Length (estimate) 2:28
Keywords QlikView getting and preparing databases
Time (mm:ss)
Shot Script
00:07 Slide: Configuring ODBC from Windows
This video will show you how to get database files and prepare them for reading into QlikView
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:34 FARS website:
http://www.nhtsa.gov/FARS
Go to the Fatality Analysis Reporting System website. This website is run by the US National Highway Traffic Safety Administration.
The data we’re going to download is data on fatal vehicle accidents in the US since 1975. This is a complex data set and records details of every single fatal road traffic accident in the US. Unfortunately, there’s a great many of these accidents.
Click on the “Download Raw Data from FTP Site”.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 41
Time (mm:ss)
Shot Script
00:15 ftp://ftp.nhtsa.dot.gov/fars/ The databases are organized by year. Let’s start with the data from 1975. Click on 1975.
We’ll use the DBF files in this project.
Download the zip file.
Open the zip file.
00:20 Zip file contents In 1975, there are three database files, ACCIDENT, PERSON, and VEHICLE. However, over the period 1975-2011, the names of these files change subtly. We also need to distinguish 1975 data from 1976 data and so on.
00:25 Directory with DBF files Move the files to a new directory and rename them.
We’ve done this for all the vehicle, accident, and person files 1975-2011 as you can see here. By renaming the files with a consistent name and adding the year, we can read all these files into QlikView using a loop.
We have made the data files available from the download site in this format – but be warned – even with compression, the files are large.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 42
Data from databases: configuring ODBC in Windows
Topics covered Windows ODBC configuration
Length (estimate) 1:40
Keywords QlikView Windows ODBC configuration
Time (mm:ss)
Shot Script
00:07 Slide: Configuring ODBC from Windows
This video will show you how to set up and name an ODBC connection in Windows. In a previous video, we showed how to download and prepare the database files to your computer. In this video, we’ll point to these files.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:12 Windows Let’s start by bringing up the ODBC dialog. There are a couple of ways of doing this, but today we’ll just use one method. Click on the Start button and type in ODBC. Click on Data Sources (ODBC).
00:04 ODBC Data Source Administrator
On the User DSN tab, click Add.
00:08 ODBC
Create New Data Source
The databases we’re using are in DBF format, so we’ll chose the Microsoft Access dBASE driver.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 43
Time (mm:ss)
Shot Script
00:11 ODBC
dBASE Setup
Set the database name to FARS. Our databases are the Fatality Analysis Reporting System, so we’ll call it FARS.
Unclick the Use Current Directory.
Click Select Directory.
00:11 Select Directory Choose the directory where the database files are located. Click OK.
Click OK.
We’ve now created a named ODBC connection.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 44
Data from databases: connecting to ODBC and loading data
Topics covered ODBC in QlikView
Length (estimate) 2:49
Keywords Configuring and loading ODBC data in QlikView
Time (mm:ss)
Shot Script
00:07 Slide:
Connecting to ODBC in QlikView
In this video, we’ll show you how to connect to an ODBC database in QlikView. The previous video in the series showed you how to copy a database to your local machine, now we’re going to connect to it.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:05 QlikView Let’s start a new QlikView application and go to the script editor.
00:07 QlikView
Script Editor
We want to connect to an ODBC connection, so we select ODBC and click Connect.
00:11 QlikView
Connect to data source
Choose the FARS connection and click on OK.
This inserts an ODBC connect statement into our script.
So how do we load data from the database?
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 45
Time (mm:ss)
Shot Script
00:40 QlikView
Script Editor
Click on the Select… button – this will help us build a load statement.
There are a large number of databases in FARS and the system is analyzing the fields in the first database, which is why there’s a short pause.
Instead of the first database, let’s choose the 2011 accident database – the file is acc_2011.
When we highlight the file, QlikView analyzes the fields in the database. In this case, there are a large number of fields, so there’s a short pause while the analysis is done.
We can load individual fields or every field – the *.
For now, we’ll load in everything – so click on OK.
00:12 QlikView
Script Editor
Load the data – control R.
This file contains data on all the fatal vehicle accidents in the US in 2011 – unfortunately, it’s a big file, so it takes a few seconds to load.
00:07 QlikView Let’s have a look at the data model it’s created – type in control T – and we can preview the data.
00:17 QlikView
Script Editor
This is the table name, which isn’t helpful. We’d like to choose our own table name, so let’s add the name to load statement.
Just before the load statement, type in Accident:
Now reload the data.
If we look at the data model, we can see the table is now called Accident.
00:16 Slide
Reading in all the data using loops
The data set consists of accident, person, and vehicle data from 1975 to 2011.
In the completed project, we use loops to read in the data. To see this in action, download the complete project and look at the load script for the loops in the accident, person, and vehicle tabs.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 46
Time (mm:ss)
Shot Script
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 47
Data from databases: field names that change
Topics covered Field names that change in QlikView
Length (estimate) 3:55
Keywords Field names that change in QlikView
Time (mm:ss)
Shot Script
00:07 Slide:
Coping with field names that change
In the real world, we face problems with data and data formatting. In this video, we’ll look at how we cope with field names that change over time.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:17 Slide
Illustrating changing field names
Here’s a real case of field names that change taken from US vehicle accident fatality data.
Each year’s data is contained in a separate database file, but the names of some of the fields change over time as shown here. In the vehicles files, the names of the fields for the fuel code and the number of occupants change. For example, the vehicle fuel type field was called FLCD_TR from 1975-2009 and FUELCODE from 2010 onwards.
In the complete data set, there are a number of other fields that change their name over time.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 48
Time (mm:ss)
Shot Script
We need consistent field names for analysis, so what can we do?
1:10 Slide
Flow chart
In general, we use variables to hold the values of things that change. We’ll do that here. Let’s use the fuel type as an example.
Conceptually, this is what we want to do. We want to create a variable, say vFuel, and set it to FUELCODE if the database has a field called FUELCODE and to FLDCD_TR if the database has a field called FLDCD_TR. We then want to load the data using the variable instead of the field name. We can load vFuel as Vehicle Fuel type, giving us a consistent field name throughout the whole period.
It sounds simple enough, but there’s an issue. In the finished application, we don’t load all the fields in from the databases. We only load in named fields. So we have a problem, we need to read in the fields to figure out what fields we need to rename, but we need to know the field names to read them in in the first place.
The solution is to load in all of the fields into a temporary table, but just the first line. This is a fast operation. We can then see what fields are in the temporary table and set the variables appropriately. Once the variables are set, we can use them to read in the data for real. We read in the vehicle table using variable names and rename the field to something consistent.
Let’s look at the code that does this.
00:45 QlikView
Script Editor
We cycle through the loop from 1975 to 2011.
On each pass, we construct the file name – there’s one vehicle database per year.
Into a table called temp, we read in all the fields but just the first line of the database.
Next, we’ll look at every field in the table temp.
We’ll check to see if the field is one of the fields that change their name. If it is, we set a variable.
The variables now hold the names of the fields we want to load.
We load all the rows in the database, but only the fields we want. We use the variables to load in the field names that change. We rename these fields to a consistent name.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 49
Time (mm:ss)
Shot Script
So what we’ve done is to give a consistent name to a changing field name.
00:30 QlikView Now let’s load the data – Control R.
This is a large data set – we’re loading in details on every single car involved in a fatal vehicle accident from 1975 to 2011. This is every car that was damaged in some way or was in some way involved in the crash. Typically, an accident will involve multiple vehicles. So there’s a lot of data to load.
To speed this up, we could save the data to QVD files and load from QVD files on subsequent loads.
00:10 QlikView The data has finished loading –we can see there are over 2 million records.
Let’s look at the data model –Control –T. We can see there are a large number of fields.
00:09 Slide
Summary
So what we’ve done here is read in 37 databases – one for each year. Some fields in these databases change their names over time. We’ve read those fields in and given them a consistent name.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 50
Data model: making new fields
Topics covered Making new fields
Length (estimate) 3:17
Keywords Making new fields QlikView
Time (mm:ss)
Shot Script
00:07 Slide: Making new fields
This short video shows how you can make new fields when loading data into QlikView. I’ll show you how you can split data from one field into several fields.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:16 QlikView Let’s start with this QlikView application. It’s a histogram of the US population from 1970-1979. We can select the year, the State, and the race/sex to look at.
Let’s look at the data model using Control T.
00:16 QlikView
Data model
There are various fields in the US Population table. We have this field call Race/Sex Indicator – it would be nice to split that into a Race field and a Sex field.
That’s what we’ll do now.
Let’s go to the script (Control E).
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 51
Time (mm:ss)
Shot Script
00:45 QlikView
Script Editor
Here’s our script.
Some of the field names are a bit verbose, so we can rename them. Let’s change Year of Estimate to be Year, and State Name to be State.
To split the field Race/Sex, we’ll use the subfield function. We’ll create two fields, one for race and one for sex. The -1 in the Sex field is because we’re selecting the last word in the field, which is always the Sex.
Later on, we’ll use this data as part of a much larger project. We’ll be using Year and State as the key, so we’ll need to create a new YearState field.
00:27 QlikView
Data model
Let’s reload the data (control-R) and look at our new data model, Control T.
As we can see, the fields have been renamed and there are two new fields, Race and Sex. There’s also a field call YearState which we’ll use as a key later.
Looking at the preview, we can see the first thousand rows.
Let’s add Race and Sex as List Boxes.
00:25 QlikView Right click anywhere and select New Sheet Object… List Box. Add a list box for Race and for Sex.
Let’s look at the effect of Race and Sex on the population chart.
00:13 Slide
We’ve created new fields.
We’ve created several new fields from existing fields and loaded them. We renamed some existing fields to be something more usable.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 52
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 53
Data model: linking different tables – keys
Topics covered QlikView keys
Length (estimate) 5:54
Keywords QlikView keys
Time (mm:ss)
Shot Script
00:07 Keys to link tables This short video explains why keys are necessary to link tables and how keys can be efficiently created.
Keys are a very basic concept in database design and there are a number of good books and websites that will explain how they work. None of the concepts discussed in this video are unique to QlikView.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
QlikView This application loads into two data sets from the US Bureau of Economic Analysis: per capita GDP by State and per capita personal income by State.
We’ve loaded it in in a way that keeps the two data sets separate. We have two sets of States. Clicking on Utah in the personal income box list box does not select Utah in the GDP list box
QlikView
Data model
Here’s our data model – control T – note there’s no link between these two data islands.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 54
Time (mm:ss)
Shot Script
QlikView In our application, we can look at GDP and personal income separately, but what happens if we want to investigate the relationship between them?
Because there’s no link between the islands, we can’t plot a chart of GDP vs. personal income for example.
QlikView
Data Model
To link together tables, we need a field in common across both tables – this is the key field.
We have two obvious candidates as keys: State and Year.
For now, we’ll choose Year as the key.
QlikView
Script Editor
In the script editor, change the name YearGDP to Year, and the name YearPersonalIncome to Year.
Reload the data.
QlikView automatically tries to link links tables that have one or more field names the same. In this case, both tables have a field called Year so QlikView links them.
QlikView
Data Model
Looking at the data model, we can see there’s a link between the tables.
QlikView Let’s update the charts to use the new Year field as a dimension.
However, we’re still using different State fields in the different tables. What we want to do is make State common – so why not make State a key too?
QlikView
Script Editor
Let’s change both tables to use the name State. Let’s reload the data.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 55
Time (mm:ss)
Shot Script
QlikView is warning us about a synthetic key.
Having two or more keys linking tables can cause problems, so to prevent those problems, QlikView creates a synthetic key when two or more fields link tables. The synthetic key is a concatenation of the two or more common fields.
For small data sets, synthetic keys aren’t a problem, but for large data sets, they can cause performance problems.
We’ll remove the synthetic key by creating our own key – a combination of year and State.
QlikView
Script Editor
In the GDP table, we’ll create the field YearState and remove the individual Year and State fields.
In the personal income table, we’ll create a new field, YearState from the year and State.
Reload the data. Note there’s no warning.
QlikView
Data model
Here’s the new data model. One field, YearState, links the tables. In turn, YearState is linked to Year and State.
QlikView This time, when we select State and year, both graphs update.
QlikView Because we have a key in common between the fields, we can now also look at the relationship between GDP and personal income.
Let’s build a chart of GDP against personal income so we can examine the relationship. For the sake of time, I’ve speeded the video up a bit here.
We can select state and year and see the effect on the chart. We can only plot these two quantities against each other because there is a key linking them.
Slide
Linking tables by a key
Avoiding a synthetic key.
We’ve taken two data islands and linked them by a common field, enabling a deeper analysis to take place. To avoid the creation of a synthetic key, we’ve created a key from two existing fields to link tables.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 56
Time (mm:ss)
Shot Script
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 57
Data model: avoiding circular references
Topics covered Building a data model that avoids circular references
Length (estimate) 6:54
Keywords Circular references QlikView
Time (mm:ss)
Shot Script
00:07 Slide
Avoiding circular references
Circular references are a problem in any tool that handles complex data. They are a problem for spreadsheets, databases, and QlikView. In this video, we’ll give an example of a data set that has circular references built in and we’ll show you how we removed them.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
00:25 Slide
Circular reference diagram
A circular reference arises when there is a circular path between tables in a data model. In other words, by following the keys that link tables, you can get back to where you started from.
Circular references can cause severe problems, including data inconsistency, performance degradation, and even crashes.
The bottom line is: we want to avoid them in our data model.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 58
Time (mm:ss)
Shot Script
00:17 QlikView
Loosely coupled
If QlikView detects a circular reference, it will give a warning and make one or more of the tables loosely coupled but we won’t go into what that is in this video.
In general, it’s better not to have circular references, so we’ll focus on getting rid of them.
00:10 QlikView
FARS
In this video series, our large application pulls together data from multiple sources to provide insight into fatal vehicle accidents in the United States.
00:27 Slide
Accident/person/vehicle
One of these sources is a set of US vehicle fatal accident data. There are three database types in this dataset:
Accident – which contains details of where the accidents occurred
Person – which contains details of all the people involved in the accidents
Vehicle – which contains details of all the vehicles involved in the accidents
The databases are linked, but unfortunately, there are multiple circular references.
00:30 Slide
Accident/person/vehicle – repeated data
The first problem is repeated fields.
Several data items are repeated across tables, for examples the field called VE_FORMS - which is the number of motor vehicles in transport – is in the accident, vehicle, and person tables. It’s the same data. We only need it once, so we’ll load it into the accident table, but not the others.
Repeated fields are the easiest type of circular reference to solve – we just load the data once.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 59
Time (mm:ss)
Shot Script
00:45 Slide
Circular key fields
However, this data set has a far worse problem. The way this data set is designed, circular references are built in. Here’s the problem.
Each accident is assigned a unique identifier called ST_CASE, so ST_CASE is a field in the Accident database.
One or more people are associated with each accident, so ST_CASE is a field in the Person database.
Each person has a unique identifier, called PER_NO. A person can be in a vehicle, in which case, there is a field called VEH_NO that points to an entry in the Vehicle database.
In the vehicle table, there is a link back to the case (ST_CASE) and a unique vehicle number – called VEH_NO.
The net result is a circular reference.
One solution might be to have a whole-part hierarchy. For example, to say that an accident has vehicles and people are in vehicles. But there are problems. In a hit and run accident, we could have person details but not vehicle details, pedestrians aren’t associated with any vehicles, and it’s also possible for a vehicle to be abandoned after an accident, meaning people aren’t associated with vehicles.
So, we need another approach.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 60
Time (mm:ss)
Shot Script
00:45 QlikView
Data model
We created a link table to remove the circular references..
The link table is a table that contains Case, Person, and Vehicle identifiers. In the link table, each case is associated with one or more people, and zero or more vehicles.
The case number links the link table to the accident table.
The person number links the link table and the person database.
The vehicle number links the link table to the vehicle database.
We removed the case number from the vehicle and person tables, and we removed the vehicle number from the person table.
All of the links now go through the link table and there are no circular references.
00:05 QlikView
Data model - preview
Here’s a preview of the link table contents.
00:44 QlikView
Script Editor
Here’s how we did it in the script.
We added the year to the case number (ST_CASE) to create a globally unique case number key.
Note we could have used the autonumber function here. Autonumber is more efficient in practice and results in faster access, but it makes things a little harder to follow in a tutorial like this.
In the Person table, we create two temporary keys – PVehicleNumber and PCaseNumber – we need this to construct the link table.
In the Vehicle table, we create a VehicleNumber key and a temporary VCaseNumber key.
00:28 QlikView
Script Editor
Here’s where we create the link table. The case number, person number, and vehicle number are in the person table, so we load them in.
However, there are some vehicles that have no people, so we need to join this data from the vehicle data file.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 61
Time (mm:ss)
Shot Script
Our link table now links every person and every vehicle to an accident.
We now need to do some clearing up, so we need to drop fields we don’t need anymore.
00:25 QlikView
Data model
Here’s the data model we end up with.
The case number links the accident table to the link table.
The person number links the case number in the link table to the person in the person table.
The vehicle number links the case number in the link table to the vehicle table.
Accident, people, and vehicle are linked via the link table.
As you can see, there are no circular references here.
00:24 Slide
Removing circular references
So we started with a data set that had circular references wired in.
We got rid of some of them by removing duplicate fields.
We managed to get rid of the rest of the circular references by introducing a data link table.
In general, if QlikView reports you have a circular reference in your data model, you should spend some time figuring out how to remove it.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 62
Data model: TRACE: telling the user how a load is working
Topics covered QlikView Trace
Length (estimate) 2:42
Keywords QlikView Trace
Time (mm:ss)
Shot Script
00:07 Slide:
TRACE seeing what’s going on during a load
Sometimes we want to understand what’s going on during the load process. We might want a progress report, or we might want to diagnose an error. This is where the TRACE command is helpful. That’s the subject of this video.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 63
Time (mm:ss)
Shot Script
1:00 QlikView
Script Editor
We can use the TRACE statement to print out text or values. Here’s how we use it in the FARS application.
First, we use it to print out a heading.
In the accident, vehicle, and person loads, we print out the name of the data set we’re loading – e.g. accident, vehicle, or person - and we print out the year – a variable. This gives us some indication of how far along we are.
Similarly, we use the TRACE in the Population, GDP, and Personal Income parts of the load script to announce what we’re loading.
The last tab shows how TRACE can be used to print out variables to summarize performance.
Here, we want to know how many rows have been loaded in each section of the load script. We create a variable for each section and count how many rows have been loaded – this is the NoOfRows function. We create a text string stating how many rows have been loaded and print that out using TRACE.
00:30 QlikView Let’s see the TRACE statements in action, let’s reload the data – control R.
We can see the title and headings we created with TRACE. We can also see the years we printed out.
Of course, not everything on the output here comes from the TRACE statement, some of it comes from the load process. We’re using TRACE to augment the existing output to provide more detail about what’s going on.
We’ll come back to this dialog box when our load has progressed a bit more.
00:10 QlikView The load script has now finished. We can see the results of the TRACE statements in printing out the summary information here.
00:08 Slide So we can use TRACE to:
Print out headings and progress reports Print out the value of variables Print out summary data.
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 64
Time (mm:ss)
Shot Script
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 65
Putting it all together
Topics covered QlikView Tutorial Overview
Length (estimate) 2:59
Keywords QlikView Tutorial – putting it all together
Time (mm:ss)
Shot Script
00:07 Slide In this short video we’ll show you the completed application running.
00:05 Slide:
Video series title
This video is part of a series of videos on developing QlikView applications.
00:10 QlikView
Completed application
Throughout the series, we develop a fully featured QlikView application that extracts data from many sources, manipulates the data, and uses advanced visualization methods.
00:09 Slide
Programmers and non-programmers
This video series is aimed at application developers who have some programming experience. Of course, you don’t have to have programming experience to use QlikView.
00:09 Web page
Landing page
For the video series you are watching now, you can download the files used in this video, the completed application, and the video scripts from this web page.
Folders Here’s where the disk-based data is on my machine
ODBC Here’s the ODBC connection – I’m using SQL Server Express and connecting it to the DBF files
Web pages Here are the web pages I’m going to read in
Load script Here’s the load script – there’s a tab for each data source plus tabs for other operations
Here’s where we create the link table
Here’s where we report on what was loaded
Loading operation Here’s the load script running.
Application This is the overview dashboard…
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 66
Time (mm:ss)
Shot Script
Application …we can see what factors affect accidents…
Application …we can look at who was involved in accidents…
Application …we can look at State data – and look for relationships, like the relationship between accidents per capita and personal income per capita…
Application …we can view base-year charts…
Application …and look at the population data…
Application Overall, we can investigate what factors are involved in fatal vehicle accidents using this application.
This application is a complex and fully featured applications using multiple data sources, multiple visualizations, and extensions.
If you’re building a full QlikView app, there might be something here that can make the process of building your application faster and easier. Perhaps you might want to copy part of the script or one of the visualizations.
00:15 Slide
The video series
This video is part of a series of videos on designing a complete and fully-featured application. It includes tutorials on reading in data from websites, spreadsheets, and databases, displaying data in different formats, and building a data model.
00:15 Slide
Getting the files
All of the files used in this series are available for download. This includes the files for the complete application, the files used in this video, and the video script. You can get all this content here.
00:02 Slide Thanks for watching!
QlikView 11 tutorial for developers – video scripts QlikView 11 tutorial for developers – video scripts| 67
© 2014 QlikTech International AB. All rights reserved. Qlik®, QlikView®, QlikTech®, 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.