Welcome to part 4 of this series. Here we start Qlik’s module 3 which will cover Data loading, Scripting, & Modelling. The introduction blogs before this were – Part 1; Part 2; and Part 3.
The resources I am going to use for my self paced lessons are – 11x Qlik Data loading, scripting, & modelling videos (2hr) + Allowance for notes and follow along = 4hr of time. An overview of these 9 videos is below:
Before jumping in, this set of videos mentions a few things that were foreign to me including ODBC , SAP, and ERP. A quick rundown of what I found from this link, this link, and this link, is that ODBC stands for Open DataBase Connectivity/Connector. ODBC is an API/API specification which you use to communicate with a database when you don’t want to use the DB’s native GUI. Here are the links explaining what SAP and ERPs are.
Video 1: Qlik Sense – Creating a Data Model
The first video in the list makes for a great recap on the data load process used in Qlik. The only new feature shown is how to add multiple sources of data to an app. Other than this, the video recaps bucketing data, calculated fields, and data associations.
Covered: Bucketing Data, Calculated fields, Adding multiple data sets, Associating data
Video 2: Qlik Sense – Connect to your data – ODBC and OLE DB
I had to watch this one back through a few times. It’s more technical than we’re used to so far but just as necessary. If you don’t remember 100% of the information, don’t worry, all you need to do is remember where you find this info.
Covered: ODBC Connector pack, Connect to Microsoft SQL Server
Video 3: Qlik Sense – Configuring an ODBC Data Source
This video serves as a follow-up to the last video. It goes into more detail about installing and setting up the ODBC connector and driver. It also shows you where to find the drivers should you need them. For my situation, ODBC is a commonly used method of data retrieval so this video is on my favorites list for future reference.
Covered: ODBC setup on QlikSense and Driver install
Video 4: Qlik Sense Desktop – Loading OLE DB Data
A break from ODBC! We take a look at loading data from an OLE DB. It’s a nice, short, straightforward video, outlining the steps needed to connect to a OLE DB. The steps are very similar to loading local data except there are a few more boxes to fill in.
Covered: OLE DB data load
Video 5: Qlik Sense – Connect to your data – Excel and other files
This video details loading data from share files.
Covered: Adding data from share files.
Video 6: Qlik Sense – Using Web File
This video details loading data from share files.
Covered: Adding data from web files.
Video 7: Data Load Editor Walkthrough and Example
A great introduction to the Qlik data load editor. It includes a basic example to build from, including setting variables, and using them in a basic for loop. Even without a lot of Qlik experience, it is easy to see the separated variables is a very useful piece of information.
Covered: For loops on spreadsheets, setting variables, help mode, debugger
Video 8: Qlik Sense Desktop – Converting a QlikView Data Model
This is something that I won’t be using a lot, but is helpful nonetheless. If you’re like me, you don’t need to remember how to do it, just remember it’s something that can be done.
Covered: Convert QlikView apps to QlikSense Apps
Video 9: Qlik Sense Desktop – Loading Data from QlikView Applications
Another useful resource should you ever need to convert a QlikView app to QlikSense. This time it focuses on getting the data
Video 10: Qlik SAP Connector – Installation and Configuration
I had no idea what SAP was and I expect you might not either. Here are a few useful resources (also included at the top of the page) explaining what SAP and ERPs are .
Covered: Installing an SAP connector, Configuring the SAP connector
Video 11: Using the Qlik REST Connector – Pagination and Multiple JSON Schemas
This is a great conclusion to a somewhat technically heavy module. In this video we have a look at using a REST API to gather data. We also have a better look at writing our own load scripts and joining two sets of data. I definitely recommend you follow along and try it out for yourself. Maybe even do what I did and choose different data sets to what Michael chooses.
Covered: REST API, Join data sets, Custom data load editor, REST pagination
So that’s the end of the part 4. We have covered a fair bit of ground again, so it’s time for another sanity check. What have we covered?
- Bucketing Data (video 1)
- Calculated fields (video 1)
- Adding multiple data sets (video 1)
- Associating data (video 1)
- ODBC Connector pack (video 2) Connect to Microsoft SQL Server (video 2)
- ODBC setup on QlikSense and Driver install (video 3)
- OLE DB data load (video 4)
- Adding data from share files (video 5)
- Adding data from web files (video 6)
- For loops on spreadsheets (video 7)
- setting variables (video 7)
- help mode (video 7)
- debugger (video 7)
- Convert QlikView apps to QlikSense Apps (video 8)
- Binary statement (video 9)
- Loading data to QlikSense from QlikView
- Installing an SAP connector (video 10)
- Configuring the SAP connector (video 10)
- REST API (video 11)
- Join data sets (video 11)
- Custom data load editor (video 11)
- REST pagination (video 11)
If you’ve found this helpful, part 5 will be published next week, here is a reminder of what we have learned so far:
Beginners guide to learning Qlik Sense – Part 1
- Know the difference between Dimensions and Measures (resource 1)
- Know what each of the menus to the left of the edit screen do (video 1)
- Add data to our Apps (video 1, 3)
- Creating Apps, Sheets and Visualisations (video 1, 3, 5)
- Bucketing data (video 2, 4)
- Create calculated fields (video 2, 4)
- Simple edit data before load (video 3, 4)
- Style our Apps. Title, logo, name, colour etc. (video 4)
- Add alternative Measures and Dimensions (video 4)
- Form data associations (video 4)
- Create reference lines (video 4)
- Search visualizations (video 5)
- Convert Graphs (Video 5)
- Create Master Items (video 5)
- Style our visualizations layout (video 5)
- Use Data Storytelling (video 6, 7)
- Export and share Qlik apps (8)
- Global smart search of variables (video 9, webinar 1)
- Collaboration with Qlik Sense (video 11)
- Qlik Streams (video 11)
- Using Qlik on mobile devices (video 12)
- Know what Green, White, and shade of Grey represent (video 10)
- In depth data load (webinar 1)
- In depth data associations (webinar 1)
- In depth visualization customization (webinar 1)
- Global selections list (webinar 1)
- Exporting sheets (webinar 1)
- Master dimensions (webinar 1)
- Write basic functions for measures and dimensions (webinar 1)
- Writing custom widgets with HTML (webinar 1)
- Generating random data (webinar 2)
- Connecting Qlik and Dropbox (webinar 2)
- Embed Qlik Sense into WordPress (webinar 2)
- Installing Qlik plugins (webinar 2)
- Colour by expression (webinar 2)
- CrossTable (webinar 2)
- Class() function (webinar 2)
- Aggr() function (webinar 2)
- FirstSortedValue() function (webinar 2)
- Set analysis (webinar 2)
- Customize apps from the front end (video 1)
- Permanently alter apps you author from the front end (video 1)
- Creating Variables (video 2)
- Referencing Variables (video 2)
- Color Variables (video 2)
- Single Configurator (video 3)
- Extension Editor (video 3)
- Writing Extensions (video 3)
- iFrames, Mashup Editor, Engine API Explorer (video 3)
- Exporting to PowerPoint (video 4)
- Data Upload, Image Upload, Attach Data an App (video 5)
- Attach images to Apps (video 5, 6)
- Compress large data groups into a heat-map on a scatter-plot (video 7)
- Source data from the Qlik DataMarket (video 8, 9)
From hard data to fluid design – Scott
Read more about How to Use Qlik here
Image source Goumbik / Pixabay