Categories
Developer Teaching QlikView /en

Lesson 7 – Exercises

Overview

In this lesson you are going to repeat all you learned on your own with your acquired skills and techniques.

Task 1 – Dataloader Adventure Works

  1. Create a dataloader with a connetion to the acces file – AdventureWorksSample which can be found in “Admin/Common/Libs/QVComponents/Examples/DbExtract”
  2. Qualify the datas and save them into the DataStaging (work with variables)

Screenshot 2015-03-28 15.10.09

 

Include the data source as an ODBC system source.

Screenshot 2015-03-28 15.31.09

Screenshot 2015-03-28 15.32.59

If you are using a 64Bit system it is important to activate the “Force32Bit” option when working with 32Bit data sources. (MS Access is 32 Bit)

Your script for this first task should look something like this:

//main rider
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

/**
 * @author Philipp Frenzel <philipp@frenzel.net>
 * @version 1.0
 * @copyright Frenzel GmbH 2015
 * @depends - list of scripts that need to be executed before
 * @provides - list of output files
 * @schedule - * * * * * in the style of unix cron
 */

//set the environment
SET _ENV = "DEV";
SET _DEBUG = 1;

//here we load the global config
$(include=../../COMMON/00 Config/CONF_PATHS.txt);

//at this point we integrate the qv-components
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/Qvc.qvs);
$(include=../../COMMON/LIBS/QVComponents/QVC_Runtime/language/qvc_language_GE.qvs);

//here we are writing a comment into a LogFile
CALL Qvc.Log('Starte das Laden der AdventureWorks Access Datei','INFO');

ODBC CONNECT32 TO [AdventureWorks;DBQ=D:\qlikview\qlik-master\ADMIN\COMMON\LIBS\QVComponents\Examples\DbExtract\AdventureWorksSample.mdb];

//Load Adventures Reiter
/**
 * @dataloader tbl_demo
 * @source OLEDB
 * @version 1.0
 * Only loads one file from the databank
 */

SET varTableName = "Product";

QUALIFY "*";

$(varTableName):
LOAD 
    *;
SQL SELECT * FROM [$(varTableName)];

UNQUALIFY "*";

Trace "Saving to Path: $(PATH_DATASTAGING)";

STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF _DEBUG = 0 THEN
  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);
END IF;

Let varNoRecords = NoOfRows(varTableName);
Let msgLogFile = 'the table ' & varTableName & ' has been loaded with ' & varNoRecords &

//here we are writing a comment into a LogFile
CALL Qvc.Log(msgLogFile,'INFO');

//exit rider
EXIT SCRIPT;

If you execute the script and set all settings correctly, then the table “Product” should appear in your DEV/02 DATASTAGING – index.

Task 2 – Integration of a webdata source

There are numerous datasources on the internet, most of which can easily be integrated into QlikView. An interesting website to look around a bit is: http://www.programmableweb.com – REST – API’s are the easiest to integrate, because they are already collecting data after a single URL-request. We are going to get some weather-informations from www.data.gv.at .

Our landig point is described here in detail: http://at-wetter.tk/index.php?men=api

In the end the script should start like task 1 and the loading rider like the following:

/**
 * @dataloader tbl_demo
 * @source OLEDB
 * @version 1.0
 * Only loads a single file from the databank
 */

SET varTableName = "WeatherData";

QUALIFY "*";

$(varTableName):
LOAD @1 AS WeatherStationId, 
     @2 AS Labeling, 
     @3 AS Height, 
     @4 AS Date, 
     @5 AS Time, 
     @6 AS Temperature, 
     @7 AS Unit, 
     @8 AS TimeStamp
FROM
[http://at-wetter.tk/api/v1/t/2015-03-01/2]
(txt, utf8, no labels, delimiter is ';', msq);

UNQUALIFY "*";

Trace "Saving to Path: $(PATH_DATASTAGING)";

STORE $(varTableName) INTO "$(PATH_DATASTAGING)$(varTableName).qvd" (qvd);

IF _DEBUG = 0 THEN
  Qvc.Log('Deleted the Table as we are not in Debug.');
  DROP TABLE $(varTableName);
END IF;

Let varNoRecords = NoOfRows(varTableName);
Let msgLogFile = 'The table ' & varTableName & ' has been loaded with' & varNoRecords &

//here we are writing a comment in a LogFile
CALL Qvc.Log(msgLogFile,'INFO');

Screenshot 2015-03-28 17.13.22

 Task 3 – Load the weatherdata of all days of the month 03

Autonomous working- 15 min time.

Leave a Reply