QlikView Designer and Developer
1
What we are going to learn 1.Glance Meet Ql QlikView 2. See eein ing g is is Bel Belie ievi ving ng 3. Data Sorces !. Data Mo Modeling ". St#ling $p %. B Bil ildi ding ng Da Dash sh&o &oar ards ds '. Scripting (. Da Data ta Mo Mode deli ling ng Be Best st )r )rac acti tice ces s *. Ba Basi sic c Data Data +ra +rans ns,o ,or rat atio ion n 1./dvanced 0pressions 11.Set /nal#sis and )oint in +ie eporting 12./dvanced Data +rans,oration 13.More on Visal Design and $ser 0perience 1!.Secrit# 1".Macros and /toation 2
What we are going to learn 1.Glance Meet Ql QlikView 2. See eein ing g is is Bel Belie ievi ving ng 3. Data Sorces !. Data Mo Modeling ". St#ling $p %. B Bil ildi ding ng Da Dash sh&o &oar ards ds '. Scripting (. Da Data ta Mo Mode deli ling ng Be Best st )r )rac acti tice ces s *. Ba Basi sic c Data Data +ra +rans ns,o ,or rat atio ion n 1./dvanced 0pressions 11.Set /nal#sis and )oint in +ie eporting 12./dvanced Data +rans,oration 13.More on Visal Design and $ser 0perience 1!.Secrit# 1".Macros and /toation 2
Modle 1 Meet QlikView /,ter copleting this4 #o will &e a&le to know Discover what QlikView is, how it's diferent rom rom other tools Explore and interact with our Explore ou r data within a QlikView document The various technical components that QlikView QlikView consists o
5n +his Modle6.. !hat is QlikView" Explorin# data with QlikView The technolo#$ and components %ehind QlikView
&
What is QlikView7 QlikView is a tool that ena%les access to inormation in order to anal$e this inormation, which in turn improves and optimies %usiness decisions and perormance( )* has %een ver$ much *T+driven( *T departments were responsi%le or the entire )usiness *ntelli#ence lie c$cle, rom extractin# the data to deliverin# the nal reports, anal$ses, and dash%oards( -tatic reports, most %usinesses nd that it does not meet the needs o their %usiness users( .s *T ti#htl$ controls the data and tools, users oten experience lon# lead+times whenever new /uestions arise that cannot %e answered with the standard reports( 0
8ow does QlikView di9er ,ro traditional The$ aim to put the tools in the hands o %usiness users, B57 allowin# them to %ecome sel+sucient %ecause the$ can perorm their own anal$ses(
/ssociative ser eperience !here traditional )* solutions use predened paths to navi#ate and explore data, QlikView allows users to take whatever route the$ want( !hile in a t$pical )* solution, we would need to start %$ selectin# a 3e#ion and then drill down step+%$+step throu#h the dened drill path, in QlikView we can choose whatever entr$ point we like43e#ion, -tate, 5roduct, or -ales 5erson( QlikView's core technolo#ical diferentiator is that it uses an ineor# data odel, which stores all o its data in 3.6 instead o usin# disk( !here traditional )* suites are oten implemented top+down4%$ *T selectin# a )* tool or the entire compan$4QlikView oten takes a %ottom+up adoption path( 7
0ploring data with QlikView 8ou can download QlikView's 5ersonal Edition rom http9::www(/likview(com:download
;
:avigating the docent 6ost QlikView documents are or#anied into multiple sheets( These sheets oten displa$ diferent viewpoints on the same data, or displa$ the same inormation a##re#ated to suit the needs o diferent t$pes o users =avi#atin# the diferent sheets in a QlikView document is t$picall$ done %$ usin# the ta%s at the top o the sheet
<
Slicing and dicing #or data .n$ selections made in QlikView are automaticall$ applied to the entire data model(
1. ;ist-&oes 2. Selections in charts 3. Search
1>
Bookarking selections )ookmarks are used to store a selection or later retrieval To create a new %ookmark, we need to open the /dd Bookark dialo#( This is done %$ either pressin# Ctrl ? B or %$ selectin# Bookark @ /dd Bookark rom the menu !e can retrieve a %ookmark %$ selectin# it rom the Bookarks menu Asin# the
11
; icon in an o%ect's header( 12
+echnolog# ?
memor$ data%ase( Compared with a disk+%ased data%ase, this ofers a #reat advanta#e when it comes to perormance Cheap memor$ and the advancement o &+%it processors and operatin# s$stems Clever compression9 QlikView utilies some sophisticated compression al#orithms to si#nicantl$ reduce the amount o memor$ that is re/uired to store data( T$picall$, on+disk data is compressed to 1> percent o its ori#inal sie when it is loaded into QlikView(
These two actors make it possi%le to create QlikView applications that contain hundreds o millions4even %illions4o records( 1
+he wa# the data @ows 1( *t starts with the source data( QlikView can load data rom a lar#e variet$ o sources 2( Data is loaded into QlikView usin# a load script( This script can %e used to extract, transorm, and load data into the in+ memor$ model or to store it to the disk in intermediar$ data les called QVD Ales ( Data in the in+memor$ data%ase is stored in an un a##re#ated ormat, meanin# all a##re#ations are calculated on the $( &( -elections made %$ the user automaticall$ cascade throu#hout the entire data model and these chan#es are shown %$ QlikView's presentation en#ine( 0( QlikView applications can %e presented in multiple clients( 1&
QlikView =nctional verview
10
When QlikView se epands .s the use o QlikView expands, keepin# track o diferent versions, dealin# with hu#e amounts o data, reloadin# and distri%utin# applications, and makin# sure that onl$ the ri#ht people have access to applications %ecomes increasin#l$ hard when usin# onl$ the !indows client( QlikTech ofers a lar#e ran#e o components which ensure that QlikView can scale rom a local deplo$ment on a laptop all the wa$ to an enterprise+wide solution These components can %e classied into three classes 1( Create content 2( 3eload, pu%lish, and distri%ute content ( Consume content 1
QlikView
17
eload4 p&lish4 and distri&te content QlikView )&lisher + This component can take care o reloadin#, reducin#, and distri%utin# the QlikView documents( Fo%s can %e scheduled or tri##ered %$ external events( QlikView Server + This is a centralied server which can load QlikView documents into memor$ and allows clients to interact with these documents remotel$ %$ usin# one o the QlikView clients( *t also has the advanta#e o clients not needin# hu#e amounts o 3.6 and C5A cores( C Clients do not need to load all o the data locall$ and the processin# power o the server is used or calculatin# and a##re#atin# data( 1;
Modle eview and +ake /wa# To sum it all up, in this chapter, we have learned 1( QlikView is a )usiness *ntelli#ence solution that is diferent rom traditional )* solutions %ecause o its associative user experience, its underl$in# technolo#$, and its t$pical %ottom+up adoption path 2( have had a peek at the various technical components that QlikView consists o ( Iow to make sense o our data9 %$ selectin#, lterin#, searchin#, %ookmarkin#, c$clin#, and drillin#( &( Iow the technolo#$ %ehind QlikView works, how it is deplo$ed, and the various wa$s in which applications can %e consumed(
2>
Modle 2 Seeing is Believing /,ter copleting this4 #o will &e a&le to know Anderstand the steps re/uired in the construction process o a QlikView document *denti$ what dimensions and expressions are within the context o a QlikView document )uild charts to visualie and a##re#ate data Desi#n a %asic user interace or navi#atin# throu#h the QlikView document D
2>>7
21
5n +his Modle6.. What is SiB7 )reparing the work space
22
What is a SiB7 . -i) Jan acron$m or -eein# is )elievin#K is the proo+o+ concept session !e will %uild a QlikView document %ased on real and useul data, ocused on Ii#hCloud .irlines' line o %usiness The dataset we will use is pu%licl$ availa%le and covers inormation a%out airline operations in the A The ori#inal data les have %een downloaded rom The )ureau o Transportation -tatistics o A-. Jhttp9::transtats(%ts(#ovK -ince it will mainl$ %e ocused on technical eatures and data discover$ unctionalities, some desi#n details Jsuch as color, st$le, o%ect positionin#, and so onK have %een let out or now( D
2>>7
2
)reparing the workspace . t$pical QlikView deplo$ment scenario will include diferent t$pes o les, %ut or now we'll ust ocus on two o them9 1( The source data les 2( The QlikView document
8ands C : Sessions 1.Setting p the ,older strctre 2.
D
2>>7
2&
+he reEireents 1( =um%er o i#hts across time 2( =um%er o transported passen#ers ( .mount o transported car#o Jmail and rei#htK &( 6ost+used routes 0( .t the same time, the application should allow the user to choose airline and aircrat dimensions, as well as ori#in and destination airports, cities, and states(
2
)la#ing with list &oes . list%ox is the most %asic o all QlikView o%ects and contains all the occurrin# values or a #iven eld in the data model To start usin# this o%ect %rin# up the Sheet )roperties dialo# window %$ ri#ht+clickin# in a %lank space inside the sheet area, then selectin# )roperties6 rom the context menu( Bnce the Sheet )roperties window is open, make sure the =ields ta% is active Hrom the .vaila%le Hields list, add the Carrier =ame, Bri#in Cit$, Bri#in Countr$, Bri#in -tate, Destination Cit$, Destination Countr$, Destination -tate elds to the Hields Displa$ed in List %oxes list on the ri#ht %$ hi#hli#htin# each o them and clickin# on the .dd M %utton No to the ;a#ot menu and click on earrange Sheet &Fects and the$ will %e dispersed across the screen space( 2;
/ssociating additional ta&les *n order to add more ta%les to the data model, we need to add the correspondin# Load statements to the script .ctivate the ta% on the ar ri#ht, which should %e the one named Main Data, and select +a& @ /dd +a&6( *n the +a& enae Dialog window, t$pe .irlines and click on ( 5ress Ctrl ? T to %rin# up the +a&le Viewer window, which shows the newl$ constructed data model( The rule or two ta%les to %e linked is simpl$ that the$ must share a eld with the same name Two ta%les should %e linked %$ onl$ one eld( * the$ have two or more elds in common, a S#nthetic e# will %e created 8ands on session ;oad additional +a&les 2<
Modle eview and +akeawa# !e have ust started creatin# and %uildin# anal$tical applications with QlikView( .lthou#h the data model we used was simple to %uild, we covered the %asic concepts a developer should consider when desi#nin# it Create %asic o%ects, and to chan#e diferent properties to make them more unctional Iow to load source ta%les rom QVDs, to associate diferent source ta%les to create a data model, and to identi$ dimensions and expressions in the context o a QlikView document( how to create a user interace with user controls to lter data and make selections( Iow to create charts and ta%les that have a hi#h de#ree o interactivit$(
1
Modle 3 Data Sorces /,ter copleting this4 #o will &e a&le to know Iow to load data rom diferent sources
•
O Iow to extract data usin# the %uilt+in wiard O !hat QVD and QVG les are O Iow to load data rom disparate sources
2
5n +his Modle6.. $sing DB< and ;0 DB drivers 0tracting dataHtwo hands-on eaples QVD and QV> Ales ;oading an 5nline ta&le
Di9erent +#pes o, Data Sorces .lthou#h there are man$ diferent Data&ase Manageent S#stes IDBMSJ out there, we can, or our purposes, #roup them into three diferent cate#ories 1( Those that provide connectivit$ via BD)C:BLE D) drivers 2( Those that use proprietar$ s$stems with no standard connectivit$( ( Those that are not necessaril$ D)6-s, %ut rather have ta%les stored in plain les, such as Excel, C-V, TGT, G6L, and the like(
&
$sing DB< and ;0 DB drivers !e can access custom data sources with a connector or a driver( *n this case, we can either %uild our own custom provider, or %u$ it rom a third+part$ QlikTech provides a -otware Development Pit J-DPK to acilitate the construction o these pro#rams, sometimes even includin# sample code Two examples o third+part$ custom connectors are QVSorce9 )uilt %$ *ndustrial Code)ox, can %e used to extract data rom a lar#e num%er o we% services that are accessi%le via .5*s QlikView KDB< connector9 This connector, rom T*Q -olutions, ena%les the possi%ilit$ 0 o communicatin# with Kava
eading ta&le Ales The third t$pe o data source $ou will nd consists o the most common ta%le les, such as Excel, C-V, TGT, G6L, or even IT6L *t will %e easier to extract data rom them i the$ are constructed in the orm o a traditional ta%le, that is, onl$ rows and columns Iowever, sometimes these les could contain extra inormation that is not actuall$ part o the core ta%le Jsuch as headers or ootersK and, thereore, additional transormations via script are re/uired The a%ilit$ to read ta%le les is especiall$ useul when we want to mix inormation rom the D)6- and data #enerated %$ the %usiness user that mi#ht not %e stored in a data%ase( Hor instance, %ud#et orecasts, external market indicators, and so on(
0tracting dataHtwo hands-on eaples we will #o throu#h the steps re/uired to extract data into a QlikView document( The extraction process throu#h which we pull data into the QlikView document consists o9 1.
0tracting data ,ro MS /ccess 1.
;
;oading a ta&le Ale 1. Speci,#ing the Ale attri&tes 2. +he
'. +he reslting script <
QVD and QV> Ales QlikView Data IQVDJ les are used to extract and store data into and rom QlikView 1( *t contains onl$ one lo#ical ta%le( 2( *t uses a special al#orithm to compress the data, achievin# compression rates o up to <> percent ( Loadin# speed is an$where rom 1> to 1>> times aster than when loadin# rom a data%ase Bne o the main advanta#es o usin# QVD les is that, once $ou have a QVD on $our disk, the ta%le can %e exploited %$ more than one QlikView application The QVD le will also %e particularl$ useul when dealin# with *ncremental Load scenarios &>
QV> Ales QlikView data e>change IQV>J les are used or data input rom external s$stems into QlikView The main diference with respect to the QVD le is that QVG is a pu%lic ormat and can %e created rom external interaces *t can %e considered as the ormat in which custom data sources send data to QlikView via the custom connector Data retrieval %ecomes optimied when compl$in# with QVG specications, althou#h not as optimied as QlikView's own QVD(
&1
;oading an 5nline ta&le !ith an *nline ta%le, the data is entered directl$ into the 0dit Script window Hrom the 0dit Script window, #o to the 5nsert menu and select ;oad Stateent @ ;oad 5nline( The 5nline Data Wiard will appear 5porting docent data to the 5nline ta&le 5port Docent Data wiard( To %rin# up this wiard, #o to the +ools menu rom the 5nline Data wiard and select Docent Data The advanta#e o this approach is that $ou will not need to maintain a separate ta%le( values will %e hardcoded in the script( !henever $ou need to chan#e them, it can onl$ %e chan#ed in the script( &2
Modle eview and +akeawa# !e have covered the most %asic extraction capa%ilities that QlikView provides !e have also descri%ed the diferent data sources $ou can access rom QlikView and provided a ew tips or dealin# with input ta%les Load data rom disparate sources, and see the diferent options the$ will have at their disposal with QlikView re#ardin# data sources one o the most important diferentiators o the QlikView sotware that is, its a%ilit$ to read and process data stored in disparate sources, and com%ine them, associate them, link them, and store it all in one location Jthe QlikView documentK &
Modle ! Data Modeling /,ter copleting this4 #o will &e a&le to know !hich t$pe o data model is %est suited or QlikView The diferent RrulesR that need to %e ollowed when desi#nin# data models or a QlikView document Iow to %est take advanta#e o the associative data model to make $our documents hi#hl$ d$namic Iow to work with ta%les and the associations %etween them
&&
5n +his Modle6.. Diensional data odeling +he associative data odel +he +a&le Viewer window ;oading an 5nline ta&le
&0
Diensional data odeling E3 modelin# aims to remove all redundanc$ rom the data model( This techni/ue #reatl$ improved and simplied transaction processin# . dimensional data model is composed o a sin#le act ta%le( This act ta%le contains a compound primar$ ke$, with separate ke$s linkin# the act ta%le to the dimension ta%les( . act ta%le does not alwa$s need to contain metrics( The act and dimension ta%les are usuall$ com%ined into a star schema( a second t$pe o dimensional model in which dimensions are not necessaril$ ull$ attened( This is called a snowake schema *n an 3D)6-, the snowake schema is sometimes chosen when tr$in# to save disk space, as it removes duplicate values rom the dimension ta%les &
Single =ile Vs 0 Model
D
2>>7
&;
S+/ Schea
D
2>>7
&<
S:W=;/0 Schea
D
2>>7
0>
Diensional odels in QlikView
01
+he associative data odel The associative data model can %e said to %e e/ual in structure to a dimensional model *n a QlikView data model, all o the eld values rom all o the ta%les in the model are automaticall$ associated amon# themselves %ased purel$ on the eld names Dimension ta%les are not onl$ associated with the act ta%le, %ut at the same time the$ are indirectl$ associated with each other throu#h the act ta%le *n an associative data model, an$ eld can act as a dimension in a chart( The$ can all %e used within expressions to a##re#ate their data too( 02
/irline perations Data Model
D
2>>7
0
Gidelines ,or ta&le associations QlikView creates associations %etween ta%les in a simple and strai#htorward manner9 throu#h the eld names i we are loadin# ta%les rom several diferent source s$stems, the most %asic and undamental tool or the data model desi#n in QlikView is enaing Aelds To rename a eld, we can simpl$ use the as ke$word in the Load script to assi#n an alias to the ori#inal eld name( The Quali$ ke$word can %e used to /uali$ eld names with their correspondin# ta%le name we can use a star s$m%ol to speci$ that all su%se/uentl$ loaded elds should %e /ualied( Quali$ instruction can %e turned of at an$ point in the script with a correspondin# An/uali$ statement 0&
/voiding data odel con@icts it's ver$ likel$ we will sometimes nd one o the ollowin# two issues +he creation o, what is called LS#nthetic e#sL +he creation o, circlar re,erences in the data odel !hen an$ two ta%les share more than one common eld, QlikView creates a complex ke$, or s$nthetic ke$( This takes the orm o an additional ta%le containin# the shared elds and an additional ke$ eld added to all involved ta%les The presence o s$nthetic ke$s in a data model can cause the application to have slow response time and sometimes even consume all availa%le resources 00
Data odel with s#nthetic ke#s
D
2>>7
0
Methods to reove s#nthetic ke#s 1( !e can rename those elds that are a part o the s$nthetic ke$ %ut should not %e a part o the association %etween the two ta%les( 2( !e can remove conictin# elds rom one o the two ta%les( To remove a eld, we ust erase the correspondin# line o code rom the Load script( ( !e can create an explicit complex ke$ with the concatenation o all common elds that actuall$ represent the link %etween the two ta%les( &( .ter creatin# the new complex ke$, we can remove the conictin# elds rom either ta%le( D
2>>7
07
D
2>>7
0;
Dealing with circlar re,erences a circular reerence can also %e the result o unwanted associations in our data model !e must alwa$s have one road %etween an$ two points( Btherwise, we would %e havin# a circular reerence a circular reerence exists when the connections amon# the ta%les simulate a circle and we have two routes to #et rom an$ #iven point to another we should start %$ askin# which o the created associations are correct and which aren't 0<
+he +a&le Viewer window The ta%le viewer shows the ta%les J%oxesK and their associations JconnectorsK( !hen more than two ta%les are associated via the same ke$, it will %e indicated with a small %lue dot in the connector line The la$out o the ta%les and connection points can %e chan#ed %$ clickin# and dra##in# the header and connection point !hen hoverin# the mouse cursor over a ta%le header, a tool tip is shown to displa$ the name o the ta%le and the num%er o rows, elds, and ke$ elds when a comment is set or the ta%le Jusin# the CB66E=T T.)LE script statementK, the correspondin# comment is also shown in the tool tip >
=ield in,oration The ollowin# inormation is shown )er,ect e# )riar# e# e# S&set ratio +ags /n# coent set on the Aeld is also shown )$ ri#ht+clickin# on a ta%le and selectin# )review, a preview o the rst 1,>>> ta%le rows will %e shown
1
+a&le viewer en The Export *ma#e command save a picture o the data model -tructure o the QlikView can %e exported usin# the Export -tructure 5rint a picture o the data model or cop$ a picture o the data model to the clip%oard usin# the Cop$ *ma#e %utton The .uto+La$out eature automaticall$ arran#es the ta%les The oom level can %e set usin# the correspondin# drop+down %ox *nternal Ta%le View is the deault option and shows how the data is stored in QlikView( Iowever, -ource Ta%le View shows how QlikView reads the data, and when s$nthetic ke$s are present in the model the$ are not shown in this view D
2>>7
2
Modle eview and +akeawa# !e learned what dimensional modelin# is, how it difers rom E3 modelin#, and wh$ it is a #ood idea to use this modelin# techni/ue in QlikView !e also saw how to take advanta#e o the inormation provided %$ the Ta%le Viewer dialo#, how the associative data model reall$ works, and how it does what it does !e even #ot an overview o the %asic rules or creatin# associations in QlikView and how to deal with the conicts in data modelin#
D
2>>7
Modle " St#ling $p /,ter copleting this4 #o will &e a&le to know -ettin# up the workspace Anderstandin# and chan#in# the sheet's properties 6ana#in# our sheet o%ect's appearance OAsin# some o the most undamental o%ects or selectin# and lterin# data 5lacin#, resiin#, and ali#nin# the sheet o%ects
&
5n +his Modle6.. Design reEireent +he Docent )roperties window +he Sheet )roperties dialog Setting the o&Fect properties Working with list&oes +he Mlti Bo +he <rrent Selections Bo /dding a Bookark &Fect /ligning and resiing sheet o&Fects
Design eEireents 1( The screen resolution on which most users will access the document 2( The #eneral st$le and la$out o the document we will divide the rontend la$out into our main panels9 1(The top panel will %e used to place time+related user controls as well as lo#o 2( The let+side panel will hold a maorit$ o the list%oxes used to lter the data ( The central area will %e used to place the diferent charts and visualiations &( The ri#ht+side panel will have other special o%ects
=or ain panels - Diagra
7
Design eEireents 1( Asin# the ocial Ii#hCloud lo#o, seen %elow( This will %e visi%le at all times and rom all worksheets in the document 2( -ettin# the Backgrond , 112, 1<2 Ii#hCloud )rown 7, ;, &1
D
2>>7
;
+he Docent )roperties window
<
+he Sheet )roperties dialog .s its name implies, the Sheet )roperties dialo# can %e used to set various properties o a worksheet Bn the General ta%, the ollowin# properties are o interest +itle9 set the title that appears in the ta% row( this can also %e a calculated value( Show Sheet9 conditionall$ hide:show the sheet Sheet 5D9 *nternal *D o the sheet( Backgrond9 !e can either use the Docent De,alt option, which we set in the previous section, or override the deault %$ settin# a sheet+specic +a&
+he Sheet )roperties dialog C More ta&s +he &Fects ta& shows all o the o%ects that are present on the worksheet, even those that are conditionall$ hidden( Hrom this ta%, we can directl$ open the individual o%ect's )roperties6 dialo# or even Delete an$ o them
Setting the o&Fect properties The properties we are most interested in at this point are9 1( Caption colors 2( Caption ont )$ deault, almost ever$ o%ect in the QlikView document has a caption %ar at the top, unless we choose to explicitl$ hide it Two t$pes o caption colors can %e set9 one or when the o%ect is 5nactive and one or when the o%ect is /ctive .n active o%ect is the one on which the user has last clicked, while all o the others are inactive 8ands on session
+he
)ropagating the o&Fect appearance * set the appearance or a sin#le list%ox( To appl$ the same con#uration to all o the remainin# list%oxes, ri#ht+click on the one we alread$ con#ured, select )roperties6 rom the context menu, and #o to the ;a#ot ta%(
7&
Setting the de,alt Sheet &Fect St#le 1( Bpen the Docent )roperties dialo# %$ selectin# Settings @ Docent )roperties or %$ pressin# Ctrl ? Alt ? D( 2( 6ake sure the General ta% is active( ( -et the St#ling Mode option to .dvanced( &( -et the Sheet &Fect St#le option to Nlass( 0( Click on to appl$ the settin#s The /dvanced st$lin# mode allows us to make additional chan#es to an o%ect's st$le, such as settin# rounded corners( 70
Sheet &Fect St#le en
7
/dding list &oes :ote 9 To add an o%ect to a worksheet, there are three %asic methods9 usin# the menu, usin# the tool%ar, or usin# the Men9 )$ selectin# ;a#ot :ew Sheet &Fect +ool&ar9 )$ usin# the design tool%ar )op-p9 )$ ri#ht+clickin# on a %lank space within the worksheet and choosin# the desired o%ect rom the :ew Sheet &Fect su%menu .dd list%ox %$ ri#ht+clickin# on the worksheet and selectin# )roperties6 Jor %$ pressin# Ctrl ? Alt ? S, which is the shortcut or the Settings @ Sheet )roperties menu commandK( =ext,Bpen the =ields ta%, locate the eld under the /vaila&le =ields list, and click on /dd N to add it as a new list%ox 77
+he ;ist Bo )roperties dialog 3i#ht+click on an$ list%ox and select )roperties6 to open the ;ist Bo )roperties dialo# window
+he General ta& +itle9 -et the +itle la%el to %e diferent rom the deault eld name( &Fect 5D9 contains the name under which list%ox is known /lwa#s ne Selected Vale9 .vaila%le when a sin#le value is selected at the time we open the )roperties dialo# The Show =reEenc# and 5n )ercent check%oxes9 =um%er o times that each value appears in the active data set( !hen 5n )ercent is checked, the relative num%er o appearances versus the total is shown( 7;
+he 0pressions ta& Lets us add calculations and mini charts into list%oxes
+he Sort ta& *t ofers the option to order the data usin# an$ o the sort orders State9 This option sorts values %ased on the selection state o items 0pression9 -orts the values %ased on the result o an expression =reEenc#9 -orts the values %ased on how oten the value appears in the dataset :eric Vale9 sorts values %ased on numeric values o each item +et9 -orts the values %ased on the alphanumerical representation o each item( ;oad rder9 -orts the values %ased on the order in which the items were loaded into QlikView 7<
+he )resentation ta& Selection St#le verride9 allows or some JlimitedK variations on the selection st$le Single
+he :&er ta& *t allows us to control how the list%ox content looks %$ usin# either predened or custom ormats )$ clickin# on the
;1
+he ;a#ot ta& *mportant options on this ta% are as ollows $se Borders9 Shadow 5ntensit# Border Width onded
;2
+he
Mltiline -pos, P-pos, Width, and 8eight
;
+he Mlti Bo The multi %ox displa$s each eld on a sin#le line, alon#side a drop+down that expands to allow selections to %e made -elect ;a#ot @ :ew Sheet &Fect @ Mlti Bo.Anlike the list%ox, the Sort ta% or the multi %ox contains multiple elds and that we can set diferent sort orders or each eld
8ands on session Mlti Bo ;&
+he <rrent Selections Bo QlikView has an option to show the user exactl$ which selections are currentl$ applied to the data9 the <rrent Selections dialo# To open this dialo#, we simpl$ need to press Ctrl ? Q or select View @ <rrent Selections rom the menu %ar .llows us to interact with the selections in the ollowin# wa$s9 0rasing Alters9 The selection over that eld will %e cleared Modi,#ing selections9 we can rene our selection and select other values, ust like with a list%ox( )$ ri#ht+clickin# on each o the displa$ed lters, we can issue additional commands such as Select 0clded, Select /ll,
/dding a Bookark &Fect !hen usin# QlikView, we invaria%l$ come across some selections that we want to return to at a later time( !e can create a %ookmark %$ usin# the menu JBookarks J Bookarks @ /dd Bookark K, K, usin# the tool%ar, or %$ pressin# Ctrl ? B( .nother option is the Bookark &Fect( &Fect( This o%ect lets us create and remove %ookmarks rom within the worksheet space( 1( 3i#ht+click an$where on the worksheet and select :ew Sheet &Fects @ Bookark &Fect( &Fect( 2( Enter )ookmarks into the +itle input %ox( ( 6ark the Show eove Btton check%ox( &( Ander Btton /lignent, /lignent, select Vertical Vertical(( 0( Click on to create the %ookmark o%ect( ;
/ligning and resiing sheet o&Fects Selecting o&Fects Moving o&Fects esiing o&Fects esiing a Mlti Bo /ligning sheet o&Fects
;7
Modle eview and +akeawa# !e learned to set document, sheet, and o%ect properties( Iow to add a %ack#round to aid the rontend la$out, and also to appl$ a corporate identit$ to our document and set a deault color map( !e learned how to create and use diferent o%ects, such as the list%ox, the Current -elections )ox, the 6ulti )ox, and the )ookmark B%ect(
;<
Modle % Bilding Dash&oards /,ter copleting this4 #o will &e a&le to know The three %asic t$pes o QlikView users, and how %est to cater to their needs •
The various chartin# options availa%le in QlikView
•
Bther sheet o%ects that can %e used to add interactivit$ to our QlikView documents •
Iow to create %asic calculations
•
<>
5n +his Modle6.. $ser t#pes /ppl#ing the D/ principle
$ser t#pes The data model within a sin#le QlikView document can %e used to serve the inormation needs o a wide ran#e o users, rom the executive to the operational level .s diferent user #roups have diferent inormation needs, QlikView documents are oten %uilt usin# the Dash&oards, /nal#sis4 and eports JD/K approach 6ost QlikView users will all into more than one user cate#or$
Dash&oard sers /nal#sts eport sers <2
Dash&oard sers Dash%oards ofer a /uick, %ird's+e$e view o inormation( The$ are oten used %$ executives and middle+mana#ement to #au#e perormance o a limited num%er o e# )er,orance 5ndicators J)5sK a#ainst predened tar#ets Data displa$ed in dash%oards is usuall$ a##re#ated at a hi#h level( Drill+downs to more #ranular data !hen dash%oard users spot an anomal$ in the data, the$ ma$ simpl$ ask an anal$st to di# deeper T$pical data visualiation on a dash%oard includes speedometers and trac li#hts to provide, at a /uick #lance, the current status o the dened P5*s <
Dash&oard 0aple
<&
/nal#sts .nal$sts are the ones who reall$ di# into the data( The$ will tr$ to uncover not onl$ what happened, %ut also wh$ it happened To do this, the$ re/uire access to the complete dataset with no detail let out the$ also need to %e a%le to /uer$ it in man$ diferent wa$s T$pical data visualiations used in anal$sis include scatter, %ar and line charts, and pivot ta%les 6an$ anal$sts will also create their own visualiations whenever the$ need to answer a specic /uestion, or will make extensive use o What-5, scenarios to test and predict an outcome %ased on chan#es in certain varia%les( <0
/nal#sts - Screenshot
eport sers 3eports can serve multiple purposes or instance, the$ can %e used to provide users at the operational level with the inormation the$ need in their dail$ activities
Dash&oard eEireents ;oad =actor 9 This #ives the num%er o enplaned passen#ers versus the num%er o availa%le seats )er,ored verss schedled @ights9 This #ives the num%er o i#hts that were perormed versus those that were actuall$ scheduled /ir tie 9 time spent $in# versus total ramp+to+ramp time 0nplaned passengers Jin millionsK9 This #ives the num%er o transported passen#ers, in millions Departres per,ored Jin thousandsK9 This #ives the num%er o i#hts perormed, in thousands evene )assenger Miles Jin millionsK9 This #ives the total num%er o miles that all passen#ers were transported, in millions /vaila&le Seat Miles Jin millionsK9 This #ives the total num%er o miles that all seats Jincludin# unoccupied seatsK were transported, in millions Market Share9 This is %ased on transported passen#ers D
2>>7
<;
/nal#sis reEireents There was consensus that at least the ollowin# areas should %e investi#ated9 Trend anal$sis o the num%er o i#hts, transported passen#ers, rei#ht, and mail throu#h time( Top 1> routes %ased on the num%er o i#hts, enplaned passen#ers, rei#ht, and mail ( The num%er o passen#ers versus availa%le seats JLoad Hactor SK across i#ht t$pes( The relationship %etween transported passen#ers, mail, and the num%er o i#hts eport eEireents 9 *t was also decided that the ollowin# two reports should %e availa%le9 /ggregated @ights per onth )5s per carrier
0pressions and 0dit 0pression window QlikView expressions can %e used ust a%out ever$where throu#hout the pro#ram, rom chart expressions to expressions or settin# colors or window titles The 0dit 0pression window contains a %i# input eld in which expressions can %e entered directl$ The =ields ta% ena%les Rclickin# to#etherR an expression %$ selectin# an /ggregation unction, such as sum, av#, min, max, and the eld to which it should %e applied !hen the Distinct check%ox is marked, onl$ uni/ue values will %e considered in the a##re#ation(
=nctions The =nctions ta%, however, contains a comprehensive list o availa%le unctions, #rouped %$ =nction
Varia&les varia%les can %e used to store expressions and values( we can use an expression in man$ places, while onl$ maintainin# it in a sin#le place
5ages The *ma#es ta% makes it eas$ to select ima#es that are %uilt into QlikView, or which have %een %undled into the document via script 1>2
+he 0pression verview window !ith expressions in so man$ locations, it can %e hard to keep track o them all( This is where the 0pression verview window comes in hand$ *t is possi%le to edit an individual expression %$ hi#hli#htin# it rom the list and clickin# the 0dit %utton( )ulk updates are possi%le, usin# the =indOeplace %utton
1>
;ine
8ands on Session ;ine
1>&
/dditional line chart properties Bn the 0pressions ta%, the /cclation option can %e used to displa$ a movin# total( This means that instead o presentin# individual values, each new value is added to the sum o all previous values Bn the St#le ta%, $ou can chan#e the ;ook option o the line chart( )esides some D efects, an interestin# visualiation is the area chart The )resentation ta% ofers options to chan#e how the data is presented within the chart( Aseul options are under the ;ineOS#&ol Settings section with these options we can chan#e the ;ine Width option o the chart as well as the sie o the s$m%ols e,erence ;ines section( This option can %e used to inte#rate additional, strai#ht lines to the line chart
1>0
1>
Scatter 7
Btton QlikView allows us to execute an action, or a se/uence o actions, when a %utton is clicked( These actions can also %e chained, so that one click on a %utton tri##ers a se/uence o actions
Statistics &o . statistics &o is a convenient wa$ to /uickl$ perorm a series o statistics on a sin#le, numeric eld 1>;
Dash&oard Design eEireents ;oad =actor 9 This #ives the num%er o enplaned passen#ers versus the num%er o availa%le seats )er,ored verss schedled @ights9 This #ives the num%er o i#hts that were perormed versus those that were scheduled /ir tie 9 This #ives the time spent $in# versus total ramp+to+ ramp time 0nplaned passengers Jin millionsK9 This #ives the num%er o transported passen#ers, in millions Departres per,ored Jin thousandsK9 This #ives the num%er o i#hts perormed, in thousands evene )assenger Miles JmillionsK9 This #ives the total num%er o miles that all passen#ers were transported, in millions /vaila&le Seat Miles JmillionsK9 This #ives the total num%er o miles that all seats Jincludin# unoccupied seatsK were transported, in millions Market Share9 This is %ased on enplaned passen#ers D
2>>7
1><
D
2>>7
111
;inked &Fects
)aste Sheet &Fect creates a cop$ o the o%ect, which is independent o the source o%ect( The )aste Sheet &Fect as ;ink option, on the other hand, creates an additional instance Jor linked o%ectK o the source o%ect !hen the same o%ect is used in man$ diferent places, such as list%oxes that appear on ever$ sheet, usin# linked o%ects can make maintenance a lot more convenient B course, we can also create copies or linked o%ects on sheets other than the source sheet( To do this, instead o dra##in# the o%ect to an empt$ space on the worksheet, dra# it to the ta% correspondin# to the tar#et sheet
Beware o, deleting linked o&Fects 8ands on session ;inked &Fect 112
Gages Let's start %uildin# the dash%oard %$ addin# three #au#e charts, one showin# a #lo%al indicator o ;oad =actor , the second showin# )er,ored vs Schedled Departres ratio value, and the third showin# the /ir +ie value -ince we have alread$ created a #au#e chart with several specic con#urations, let's make use o it to create a new one without havin# to do the whole process over a#ain 3i#ht+click on the #au#e chart we created previousl$ and click on the
More Gage st#les
11&
/dding a +et o&Fect
. text o%ect can %e used to displa$ a static or calculated text and, somewhat counter+intuitivel$, ima#es as well The expression should start with an Je/ual toK si#n to treat the entered text as an expression and evaluate it accordin#l$ *t does not have the :&er properties ta% which is oten seen on other o%ects, that is wh$ we use =umJK unction to properl$ ormat )$ checkin# the Wrap +et option we can create a multiline caption Bne thin# to make note o is the ;a#er settin# .nother interestin# eature o the Text o%ect is that we can assi#n actions to it, essentiall$ makin# it unction as a %utton( 110
/dding a )ie chart
The nal metric that we want to displa$ on our dash%oard is 6arket -hare( This metric is %ased on the num%er o enplaned passen#ers per carrier, relative to the total( !e will use a )ie
Show thers
2>>7
11
=ew ore options 0nclosing the epression in parentheses :ot preAing the varia&le epression with eEals sign Dollar Sign 0pansion +he varia&le nae &egins with an e D
2>>7
117
<#clic and Drill-down grops c$clic #roups can %e used to d$namicall$ switch the dimension o chart !hen creatin# a c$clic #roup, make sure to select the <#clic Grop radio %utton rom the Grop Settings dialo# window *n the Grop Settings dialo#, the ;a&el input %ox can %e used to override the displa$ la%el o the eld( )esides elds rom the data model, an expression can also %e used to dene a eld( This eld can %e added usin# the /dd 0pression %utton 11;
Straight ta&le !hat is known as a strai#ht ta%le in QlikView is in act a re#ular ta%le( *t can contain dimensions and calculated expressions, which makes it the ideal candidate to displa$ our P5*s !hile a strai#ht ta%le can contain %oth dimensions and expressions, a ta%le %ox, which is created %$ selectin# ;a#ot @ :ew Sheet &Fect @ +a&le Bo rom the menu %ar, can onl$ contain dimensions . nice eature o strai#ht ta%les Jand pivot ta%les as wellK is that not all expressions need to %e num%ers, )$ deault this is set to +et, %ut there are other interestin# options =ote that when ta%les are exported to Excel, ima#es such as #au#es or mini charts will not %e included in the export( D
2>>7
11<
)ivot ta&les 5ivot Ta%le ofers more exi%ilit$ over a strai#ht ta%le when workin# with multiple dimensions Bne o the advanta#es o pivot ta%les is the a%ilit$ to not onl$ list dimension values as rows, %ut displa$ them as columns as well, creatin# a cross+ta%le )ivot +a&le Vs Straight ta&le *n a pivot ta%le, expressions can %e Rrolled upR with su%totals *t is possi%le to drilldown to a deeper level %$ clickin# on the expand icons . cross+ta%le can %e created %$ dra##in# dimensions 12>
+he eport 0ditor window The eport 0ditor window lets us desi#n static reports that can %e used or printed distri%ution or saved to 5DH les Bther options in the eport 0ditor window to take note o Single verss lti page9 !hen creatin# a new pa#e or a report, we can decide i it should %e sin#le pa#e, or multi pa#e eport Settings 8eaderO=ooter9 *t is used to set headers and ooters or our report eport Settings Selections9 *nstead o %asin# our report on the current selection in our document, we can also clear all selections or dene a %ookmark as a startin# point 121
ther charts C adar
122
Mekko
12
Grid
12&
=nnel
120
Block
12
+rellis
127
Modle eview and +ake /wa#s hopeull$ achieved a deeper understandin# o data visualiation in QlikView and amiliaried $oursel with the %asics o %uildin# rontend dash%oard, anal$ses, and reports in QlikView !e started with the /nal#sis sheet, or which we created %asic data visualiation o%ects like %ar, line, com%o, and scatter charts !e %uilt the Dash&oard sheet, where we learned how to create #au#es, text o%ects, and pie charts, while also learnin# a%out linked o%ects, actions, and dimension limits The nal sheet that we %uilt was eports here we learned how the strai#ht ta%le, pivot ta%le, and ta%le %ox o%ects are created( .dditionall$, we also learned a%out varia%les, c$clic and drill+ down #roups, auto minimiin#, and the 3eport Editor 12;
Modle ' Scripting /,ter copleting this4 #o will &e a&le to know The -cript Editor
•
O The most important script statements and how to use them to manipulate ta%les and control the ow o the script O Bperators and unctions or dealin# with various data t$pes O The options or de%u##in# $our script O Iow to or#anie and standardie $our script O Iow to re+use $our script 12<
5n +his Modle6.. +he Script 0ditor Script stateents Dealing with di9erent data t#pes De&gging script Standardiing and organiing script e-sing scripts 1>
+he Script 0ditor !hen $ou've opened the document, let's open the script editor %$ selectin# =ile @ 0dit Script rom the menu or %$ pressin# Ctrl ? E 8ou will notice that the script editor consists o the ollowin# areas9 Men and +ool&ar 9 The menu ofers a wide ran#e o options, or some o which the tool%ar ofers shorthand icons Script pane 9 6ain workin# area o the script editor( Lines are num%ered and that the editor has s$ntax hi#hli#htin#( *t is important to note that QlikView statements alwa$s end with a +ool pane The =nctions ta% #ives a cate#oried overview o all the script unctions within QlikView( The 11
Script stateents . QlikView script is made up o a se/uence o statements( These statements are are t$picall$ used to either manipulate the data, or to conditionall$ control the wa$ in which the script is executed Hor example, example, we ma$ want to com%ine two ta%les to#ether, or skip over a part o a script i a condition is not met *t is important to note that QlikView script is executed executed in a se/uential order( This means that script is executed top to %ottom, and let to ri#ht 8ands on Session Bilding the aircra,t diension
ta&le 12
/irline Diensional Modeling
D
2>>7
1
Maniplating ta&les LB.D statement is the main statement used to load data into QlikView The FB*= statement is a prex to the LB.D LB.D statement( *t is used to oin the ta%le that is %ein# loaded to a previousl$ loaded ta%le( The two ta%les are oined usin# a natral Foin The FB*= statement can %e prexed prexed with the statements *==E3, BATE3, LEHT, and 3*NIT The PEE5 statement keeps %oth %oth ori#inal ta%les and lters JkeepsK rows in one ta%le %ased on matchin# rows in another ta%le CB=C.TE=.TE statement appends the rows o one ta%le to another ta%le( !e can prevent this with the =BCB=C.TE=.TE statement( 1&
Koins 0aple
D
2>>7
10
Varios Koins 0aples
D
2>>7
1
$sing M/))5:G ta&les The 6.55*=N statement provides an alternative to the FB*= statement in a ver$ specic scenario9 when $ou want to replace a sin#le ke$ value with a value rom a lookup Jmappin#K ta%le )$ prexin# the LB.D statement with the 6.55*=N statement, we tell QlikView that we want to create a mappin# ta%le The CB66E=T statement can %e used to add comments to ta%les and elds There is an eas$ wa$ to export a QlikView ta%le to an external QVD le the -TB3E statement( 3enamin# ta%les or elds in QlikView is done usin# the 3E=.6E statement Deletin# ta%les or elds is done usin# the D3B5 statement 17
Setting varia&les . varia%le is a s$m%olic name that can %e used to store a value or expression( )esides the rontend, varia%les can also %e used within QlikView scripts QlikView has two statements that can %e used to assi#n a value to a varia%le, -ET and LET The diference %etween these two is that the -ET statement assi#ns the literal strin# to the varia%le, while the LET statement rst evaluates the strin# %eore assi#nin# it 0aple ;0+ vDate+oda# +oda#IJT 1;
1<
Dealing with di9erent data t#pes -trin#s are pieces o text in QlikView these are oten used to provide context to the num%ers( 8ou ma$ have noticed that in the script, strin#s are alwa$s enclosed %etween sin#le /uotes J'K The most common operation perormed on strin#s is concatenatin# two or more strin#s to#ether into a sin#le strin#( This is achieved %$ usin# the U operator
String =nctions LE= LB!E3 T3*6 3E5L.CE
A55E3
5A3NECI.3
3*NIT 3T3*6
C.5*T.L*E LT3*6 6*D *=DEG PEE5CI.3 1&>
LEHT
:&er and :eric =nctions QlikView supports the %asic arithmetic operators
1&1
Date and tie ,nctions
1&2
De&gging script )esides catchin# errors on+the+$, we can also run a s$ntax check over the entire document %$ selectin# +ools S#nta
+he script de&gger 1( The script rom all ta%s concatenated into a sin#le script( 2( The statement that is currentl$ %ein# executed( ( Bn the let, the script execution lo# le( This shows the same inormation as the Script 0ection )rogress dialo# window durin# normal reload( &( Bn the ri#ht, the current values o the document's varia%les( !e can run the script in the ollowin# diferent wa$s9 1( Asin# the n %utton, which will run the script in the re#ular manner to the end, or until a %reakpoint is reached( 2( !hen clickin# on the /niate %utton, the script is run in the re#ular manner, %ut a small pause is added ater each step( This wa$ the script execution can %e monitored more easil$( ( !hen the Step %utton is clicked, the script executes a sin#le statement( 1&&
Standardiing and organiing script .s we saw when we rst looked at the script editor, the script can %e split up into diferent ta%s( *t is advisa%le to divide $our script into diferent ta%s, each one ocusin# on a diferent unctional area or ta%le *t is advisa%le to comment the ollowin# thin#s9 Ta%le names, Neneral inormation, )usiness lo#ic *t is #ood practice to add an 5n,oration ta% to $our script )esides ta%s, comments, and an inormation sheet, usin# a proper la$out or $our script #reatl$ increases reada%ilit$( *t is recommended to use indentation to visualie the diferent levels in $our script( 1&0
:aing conventions *nstead o namin# our ta%le C-TWD.T., we name it Customers( 6appin# ta%les are prexed with the word 6ap( Temporar$ ta%les are ta%les that are not used in the nal data model, %ut when we use them the$ are prexed with TE65 Hield names also have a %usiness+riendl$ name( e# Aelds, elds that are used to link ta%les to#ether, are prexed with a S Measres, elds that contain amounts, are prexed with a X =lags, elds that contain a 8es:=o or 1:> indicator, are prexed with a W JunderscoreK si#n D
2>>7
1&
e-sing scripts !hen developin# QlikView documents, we oten have to appl$ the same set o lo#ic or transormations to diferent data . su%routine is a reusa%le %lock o script that can %e called rom other places in the QlikView script %$ usin# the C.LL statement This %lock is ormed usin# the -A) and E=D -A) control statements( -u%routines can contain parameters so that processin# can %e done in a exi%le manner *t is also possi%le to re+use script %etween documents %$ includin# external script les( 3e+usin# script %etween documents is a worthwhile #oal as it eases development and simplies maintenance D
2>>7
1&7
Managing Ale locations and connection strings *n our current example documents, we have alwa$s reerred to the Data Hiles older or our source data( *, or an$ reason, this older has to %e moved somewhere else, we will have to manuall$ chan#e the source data path in man$ locations in man$ les(
D
2>>7
1&;
Modle eview and +ake/wa#s !e have learned how to navi#ate the script editor( !e also picked up a ew tips and pointers or workin# with diferent data t$pes in QlikView, and or de%u##in#, standardiin#, and or#aniin# our scripts !e also learned how to or#anie and standardie our script and how to re+use $our script within and %etween QlikView documents !e learned a%out the -cript Editor window, what the most important script statements are and how to use them, operators and unctions or dealin# with various data t$pes, and what options exist to de%u# a script( D
2>>7
1&<
Modle ( Data Modeling Best )ractices /,ter copleting this4 #o will &e a&le to know 6ake sure data models are consistent
•
O !ork with complex data models and multiple act ta%les O 3educe stora#e re/uirements or a dataset O Deal with date and time inormation
10>
5n +his Modle6.. Data consistenc# edcing storage reEireents Design challenges o, data odeling +he aster calendar / Anal note on data odeling
101
Data consistenc# This is one o the most important thin#s we need to take care o when %uildin# QlikView documents 8ands on session -ometimes, a dimension ta%le can contain values that do not have an$ associated acts( it can %e ver$ useul or a %usiness anal$st to see which dimensions do not have an$ act data associated with them
D
2>>7
102
edcing storage reEireents QlikView uses an in+memor$ data%ase, it is also a #ood idea to not waste resources( 3.6 is still a much scarcer resource than disk !hen linkin# %etween ta%les usin# ke$ elds, it is advisa%le to use num%ers as ke$ values instead o text The .uto=um%erJK script unction can %e used to #enerate a uni/ue inte#er value or an expression or compound ke$, thus compactin# the occupied 3.6 space .n automated tool that can help $ou spot unused les is 3o% !underlich's Document .nal$er * possi%le, it is worth considerin# i a hi#h cardinalit$ eld can %e split into multiple low cardinalit$ elds ( D
2>>7
10
Design challenges o, data odeling !e will now provide some useul tips or dealin# with more complex data models, specicall$ those used or dealin# with multiple act ta%les in a sin#le QlikView document The desi#n challen#e we will descri%e in this section will %e aimed at inte#ratin# a new act ta%le into our data model, one that contains /irline 0plo#ent Statistics !e will present the wa$ o solvin# the task at hand9 1( )$ concatenatin# the two act ta%les into one(
D
2>>7
10&
2>>7
100
Modle eview and +akeawa#s !e learned how to deal with common consistenc$ issues, such as acts without associated dimensions, and vice versa !e learned how we can reduce stora#e re/uirements %$ usin# numeric ke$s, removin# unused elds rom our model, and %$ splittin# hi#h+cardinalit$ elds !e also learned how to approach two o the most common desi#n challen#es, concatenatin# two act ta%les and creatin# link ta%les, and what the advanta#es o the diferent methods are Hinall$, we learned how to create a master calendar
D
2>>7
10
Modle * Basic Data +rans,oration /,ter copleting this4 #o will &e a&le to know 6ake the data sources ade/uate to meet our data model desi#n re/uirements Deal with unstructured ta%les Jsuch as Cross ta%lesK and incorporate them into our data model
107
5n +his Modle6..
10;
D
2>>7
10<
L
D
2>>7
1>
ther trans,oration tricks Let's look at some other options the Transormation -tep !iard provides
2>>7
11
0panding a hierarch# . hierarch$ ta%le is a common ormat to store inormation in a parent+child structure( The hierarchical nature o the ta%le allows one value to %e related to one or more values across the ta%le, as a parent or as a child *n act, one value can %e related to one or more other values as a child and to one or more other diferent values as a parent( The advanta#e o these ta%les is that the$ keep the inormation in a compact ormat, and QlikView is a%le to handle them, and expand its relations with a special unction9 8ierarch# Each o levels should %e stored in a diferent eld in the QlikView data model ater expandin# the hierarch$ D
2>>7
12
Working with the 8ierarch# Wiard 5D =ield9 -tores the *Ds correspondin# to the child nodes )arent 5D =ield9 Hield that stores the *D o the parent node :ae Aeld9 This is the eld that stores the name o the child node )arent :ae9 This is a strin# used to name a new eld that will %e created containin# the names o the parent nodes )ath :ae9 This is a strin# used to name a new eld that will %e created containin# the list o nodes rom the top level to the correspondin# node Depth :ae9 This is the name to %e assi#ned to a new eld that will hold the num%er o levels or each expanded node )ath Sorce9 This is the eld rom the source ta%le that contains the value that should %e used to populate the hierarch$ path )ath Deliiter9 This denes the strin# that should %e used to separate the hierarch$ values in the path D
2>>7
1
8ierarch# Wiard
D
2>>7
1&
+he tree-view list-&o The tree+view list %ox onl$ re/uires a eld with the hierarchical denition or a set o values, and with each hierarchical level separated %$ a specic character
D
2>>7
10
Generic load . #eneric ta%le is commonl$ used to store attri%ute values or diferent o%ects !hen loadin# a #eneric ta%le, we can use the NE=E3*C ke$word
D
2>>7
1
Modle eview and +akeawa#s !e have seen our diferent scenarios in which the source ta%le is not suita%le or a QlikView data model, and we have shown the tools QlikView provides to deal with those ormats !e have learned how to use the Transormation !iard to remove #ar%a#e rom input ta%les, ll missin# cells, and unwrap ta%le les !e also learned what a Crossta%le is, wh$ it's not t or the QlikView data model, and how to transorm it into a traditional ta%le( !e saw how to deal with hierarchical ta%les and identi$ parent and child nodes we learned what a #eneric ta%le is and how to take advanta#e o QlikView's a%ilit$ to transorm its structure D
2>>7
17
Modle 1 /dvanced 0pressions /,ter copleting this4 #o will &e a&le to know To expand the use o varia%les To use conditional unctions and * expressions To handle advanced a##re#ations
1;
5n +his Modle6.. $sing varia&les $sing the ++/; EaliAer +he /ggr ,nction
1<
$sing varia&les Varia%les in QlikView are used in two diferent wa$s9 To store a value or strin# either static or %ased on a ormula To store an expression denition that can %e used in charts( #o to the Varia&le verview window %$ usin# the ke$%oard shortcut Ctrl ? Alt ? V or %$ clickin# on the Settings menu and selectin# the Varia&le verview Click on the /dd %utton and the :ew Varia&le dialo# will appear, where we will t$pe the name we want to assi#n to our new varia%le(
17>
5nteractivel# changing a varia&leUs Two main la$out o%ects throu#h which varia%le's value can %e vale chan#ed
$sing the 5npt Bo o&Fect The *nput )ox is %asicall$ an Excel+like cell on which the user enters values( .n *nput )ox can hold an$ num%er o varia%les, each one with its own associated cell
$sing the Slider o&Fect -imilar to the *nput )ox o%ect, the -lider o%ect is used to interactivel$ chan#e a varia%le's value rom the rontend The main diference is the wa$ in which the user interacts with the o%ect( The -lider o%ect is a little more visual !e must set min and max values to delimit the slider's ran#e D
2>>7
171
$sing varia&les &ased on Create a varia%le with a d$namic value, one that responds to the epressions document state and calculates an output value %ased on user selections -ince the varia%le's value is an active calculation, the output value will respond to all user selections The main advanta#e o usin# varia%les or handlin# chart expressions is that, when usin# the same expression across several sheet o%ects, it's easier to administer when a new chan#e needs to %e made to the expression -ometimes, one sin#le chart re/uires the use o the same expression to dene diferent properties or example, to add a text in the chart with the expression result, or to dene thresholds with diferent colors or each( D
2>>7
172
Varia&le naing convention *t's important, when workin# with varia%les, to assi#n names %ased on certain RrulesR to help %etter understand( The one %asic rule in namin# consistenc$ is the use o predened prexes !e have %een usin# a particular s$ntax to reerence varia%les it's called Dollar Sign 0pansion JDS0K( 8ou can see the role o the Dollar -i#n Expansion s$ntax as that o simpl$ evaluatin# a varia%le's contents, that is, calculate JexpandK the result o the varia%le and then return the output value *t's #ood practice to alwa$s use D-E even when the varia%le to %e expanded does not re/uire it There are, however, cases in which the D-E is inefective on its own( !hen the varia%le's output value is a text strin# D
2>>7
17
+he /ggr ,nction The output o the .##r unction can %e likened to the list o values a strai#ht ta%le would displa$ when evaluatin# an expression over a certain dimension Essentiall$, the .##r unction creates a virtual strai#ht ta%le, that we can urther process the list o values that would appear in the expression column, without even creatin# the actual o%ect +he reslt o, the /ggr ,nction can &e sed to Create a calculated dimension and perorm a nested a##re#ation 5erorm additional a##re#ations %ased on the resultin# set o values 8ands on Session 1. $sing /ggr ,or nested
aggregation 2. /ggregations over the /ggr otpt D
2>>7
17&
2>>7
170
8eat Map
D
2>>7
17
Modle eview and +akeawa#s we've %een a%le to use some advanced techni/ues or data a##re#ation and advanced expressions( !e have learned how to use varia%les in QlikView and em%ed them into expressions !e also learned the use o conditional expressions, with the * unction, to output results %ased on lo#ical comparisons Hinall$, we learned how to use advanced and nested a##re#ations in charts
D
2>>7
177
Modle 11 Set /nal#sis and )oint 5n +ie eporting /,ter copleting this4 #o will &e a&le to know -et .nal$sis and modied record sets 5oint *n Time 3eportin# OComparative anal$sis with alternate states
17;
5n +his Modle6.. +he agic o, Set /nal#sis )oint 5n +ie eporting
17<
+he agic o, Set /nal#sis -et .nal$sis is a #reat eature in QlikView that lets $ou, as a developer, take control over what $our charts displa$ and allows calculations that wouldn't %e possi%le otherwise, at least not as d$namicall$( The excessive use o -et .nal$sis in chart expressions can $ield poor perormance or low response times( Iowever, we should also know that, when used efectivel$, it can have a positive impact in %oth perormance and user experience we will cover topics such as when to use -et .nal$sis, wh$ $ou should use it, what the correct s$ntax is, and we will provide common examples and several tips and tricks or maximiin# perormance as well( D
2>>7
1;>
What is it ,or7 !ith A* selections, we can sa$ that whatever is selected afects the entire document, and all o the charts onl$ displa$ inormation associated with the set o data correspondin# to those selections plain and simple *t restricts, predenes, or extends the set o data that charts %ase their calculations on Asin# a set expression, we can, or instance, speci$ that a certain chart should perorm an a##re#ation onl$ %ased on records that meet a set o criteria in certain elds !e can also use -et .nal$sis to expand the selections made %$ the user to show, or example, results o the previous $ear even when it has not %een actuall$ selected( 1;1
Set /nal#sis These are some situations in which the use o -et .nal$sis is practical 1( To compare results or two diferent time periods in one sin#le view %ased on the same selection state 2( To restrict or exclude certain valueJsK in a eld rom the calculation ( To create a cumulative sum or 8TD J8ear+To+DateK result, even i the user selects onl$ one month &( To disre#ard selections in a certain eld that ma$ not %e applica%le to calculate a particular metric 0( To essentiall$ i#nore all selections ( To use the set o data returned %$ a )ookmark, even when the )ookmark is not activated 7( To replace * expressions that make use o lots o hardware resources ;( . mix and match o all o the a%ove
D
2>>7
1;2
S#nta and eaples !e start of %$ denin# the %ase expression( -a$ we want to sum the total num%er o i#hts perormed( -o, we will %e#in with the ollowin#9 -umJYX Departures 5erormedZK *nternall$, QlikView evaluates the eld value denitions in -et .nal$sis in the same manner as conditional expressions are evaluated J%ut usuall$ asterK, to determine i a specic record should %e part o the calculation or not( Thereore, the same rule discussed previousl$ a%out numeric versus text+%ased comparisons applies or -et .nal$sis(
D
2>>7
1;
0aples -umJYX Departures 5erormedZK -umJ[set expression\ YX Departures 5erormedZK -umJ[]^YCarrier NroupZ ['Horei#n Carriers'\M\ YX Departures 5erormedZK -umJ[]^8ear [2>1>\M\ YX Departures 5erormedZK -umJ[]^8ear [R2>_R\M\ YX Departures 5erormedZK D
2>>7
1;&
0aples -umJ[]^YCarrier's Bperatin# 3e#ionZ ['Domestic', 'Latin .merica'\, 8ear [2>1>\M\ YX Departures 5erormedZK
D
2>>7
1;0
$sing varia&les in set epressions
*t's sometimes convenient to make use o varia%les in set expressions to make them even more d$namic *, or instance, our varia%le contains a num%er, we would t$pe the set anal$sis expression as ollows9 SIXPear Iv;astPearJYNY ZR Departres )er,ored[J * we want to use a varia%le's value as a search strin#, the expression would %e9 SIXZ/ircra,t Grop[ L IvSearch/ircra,tGropsJLYNY ZR Departres )er,ored[J 1;
D#naic record sets Bur next step will %e a%out makin# our modied record set d$namic and %ased on the user's current selections, that is, usin# a calculated eld value denition( )$ doin# so, the alternative record set will d$namicall$ chan#e dependin# on what the user selects To em%ed actual calculations into the eld value denition in a set expression, we use the Dollar -i#n Expansion JD-EK s$ntax( The nal expression would %e as ollows9 -umJ[]^8ear []J6axJ8earKK\M\ YX Departures 5erormedZK * we want to #et the record set correspondin# to the previous $ear to whatever $ear the user selects, we would use9 -umJ[]^8ear []J6axJ8earK+1K\M\ YX Departures 5erormedZK 1;7
More assignent operators *n some cases, we will need to rst take the actual record set that the user has selected and modi$ it %$ addin# or removin# certain values +he availa&le assignent operators are 9 This is what we have %een usin#, and it simpl$ redenes the selection or a certain eld( ?9 This operator implicitl$ denes a union %etween the selected eld values and the ones we speci$ in the element list( O +9 This operator implicitl$ denes an exclusion o the values we speci$ rom the values the user has selected _9 This operator is used to dene the correspondin# eld values %ased on the intersection %etween what the user has selected and the values we speci$ :9 This one is used to dene a s$mmetric diference JGB3K D
2>>7
1;;
/ssignent operators - 0aples -umJ[]^8ear ? [2>>7, 2>>;\M\ -alesK + 3eturn the sales or the $ears the user has selected and also or the $ears 2>>7 and 2>>; whether the$ are selected or not -umJ[]^5roduct + ['5roduct G'\M\ -alesK + return the sales or the products the user has selected, %ut exclude records correspondin# to 5roduct G -umJ[]^5roduct _ ['5roduct G', '5roduct 8'\M\ -alesK + returns the sales or the current selection, %ut onl$ or the intersection o currentl$ selected products, and products G and 8( -umJ[]^5roduct=um%er _ [R&;_R\M\ -alesK + 3eturns the sales correspondin# to the current selections, %ut onl$ or the intersection o the currentl$ selected products and all o the products whose num%er %e#ins with &;( 1;<
Set operators
-et modiers, the part o the set expression that is enclosed in an#le %rackets, can also %e constructed %$ com%inin# several diferent element lists in the eld value denition( Hurthermore, the entire set expression can %e composed usin# several diferent set modiers( This is accomplished usin# set operators( The diferent set operators that can %e used are9 ?9 Anion +9 Exclusion _9 *ntersection :9 -$mmetric diference The exclusion operator J+K can also %e used as a unar$ operator to retrieve the complement set D
2>>7
1<>
Set Operators - Examples Let's review some %asic examples9 -umJ[]^8ear [2>>7, 2>>;\ ? [R^2>>>R\M\ YX Departures 5erormedZK + 3eturn the total i#hts perormed in the $ears 2>>7, 2>>;, plus all o the $ears that are less than or e/ual to 2>>>( -umJ[]^YCarrier's Bperatin# 3e#ionZ ['Latin .merica'\,8ear [2>11\M ? ^YCarrier's Bperatin# 3e#ionZ ['Domestic'\,8ear [2>1>\M + ^YCarrier NroupZ ['Horei#n Carrier'\M\ YX Departures 5erormedZK + 3esult with the total num%er o i#hts perormed durin# 2>11 %$ carriers operatin# in Latin .merica plus i#hts perormed durin# 2>1> %$ carriers operatin# as Domestic, %ut exclude Horei#n Carriers rom %oth sets( This is one o those calculations that wouldn't %e possi%le with simple selections( D
2>>7
1<1
)oint 5n +ie eporting Bne o the most common use cases o -et .nal$sis is )oint 5n +ie eporting Iavin# the a%ilit$ to perorm period+over+period anal$sis is a %asic re/uirement in an$ )* tool and is easil$ perormed in QlikView with the aid o set expressions 0aple <rrent Pear-+o-Date indicators verss the sae period last #ear <rrent onth verss sae onth last #ear <rrent onth verss previos onth
D
2>>7
1<2
DeAning the set odiAers
The set modiers we dene when %uildin# our expressions need to account or all o the possi%le scenarios and alwa$s show a clear result <rrent Pear-+o-Date indicators ^5eriod [R^]J6axJ5eriodKKR\, 8ear [R] J6axJ8earKKR\, Quarter , 6onth M <rrent onth ^5eriod []J6axJ5eriodKK\, 8ear , Quarter , 6onth M )revios Month *J3i#htJ6axJ5eriodK ` 1, 2K '>>', J6axJ8earK ` 1K U '12', 6axJ5eriod ` 1KK 8ands on session /to:&erIJ D
2>>7
1<
More )oint 5n +ie eporting eaples P+D IPear-+o-DateJ @ights -umJ[]^5eriod*D [R^]J6axJ5eriod*DKKR\, 8ear [] J6axJ8earKK\, Quarter , 6onth , 5eriod M\ YX Departures 5erormedZK Q+D IQarter-+o-DateJ @ights -umJ[]^5eriod*D [R^]J6axJ5eriod*DKKR\, Quarter*D []J6axJQuarter*DKK\, 8ear , Quarter , 6onth , 5eriod M\ YX Departures 5erormedZK M+D IMonth-+o-DateJ @ights -umJ[]^5eriod*D []J6axJ5eriod*DKK\, 8ear , Quarter , 6onth , 5eriod M\ YX Departures 5erormedZK D
2>>7
1<&
Storing set epressions into varia&les *nstead o writin# the set expression each time or ever$ expression in which it is used, it's a #ood practice to store its denition in a varia%le, which can then %e called rom an$where in the QlikView document where it's re/uired 1( -umJ[]^5eriod*D [R^]J6axJ5eriod*DKKR\, 8ear ] J6axJ8earKK\, Quarter , 6onth , 5eriod M\ YX Departures 5erormedZK 2( -umJ[]^]Jv-et8TDKM\ YX Departures 5erormedZK
D
2>>7
1<0
D
2>>7
1<
Modle 12 /dvanced Data +rans,oration /,ter copleting this4 #o will &e a&le to know The most commonl$ used data architectures that can ease QlikView's development and administration The availa%le unctions or data a##re#ation Iow to take advanta#e o some o QlikView's most powerul data transormation unctions(
1<7
5n +his Modle6.. Data /rchitectre ;oading data alread# stored in QlikView /ggregating data Sorting ta&les +he )eek ,nction Merging ,orces Dealing with slowl# changing diensions 5ncreental loads
1<;
Data architectre This reers to the process o structurin# the diferent la$ers o data processin# that exist %etween the source ta%les and the nal documentJsK Iavin# a well+desi#ned data architecture will #reatl$ simpli$ the administration o the QlikView deplo$ment *t also makes the QlikView solution scala%le when new applications need to %e developed and when the QlikView environment #rows There can %e a lot o diferent data architectures, %ut in this module we will discuss two o the most commonl$ used in QlikView enterprise deplo$ments D
2>>7
1<<
+wo-stage architectre
D
2>>7
2>>
+he two-stage architectre ;a#ers Sorce ;a#er9 composed o the source data%ases and ori#inal ta%les 0tract ;a#er9 composed o QlikView documents, containin# mainl$ script( These are used to pull the data rom the source la$er and store it into QVD les QVD ;a#er9 the set o QVDs resultin# rom the Extract La$er( These QVDs %ecome the data sources used %$ the nal QlikView document )resentation ;a#er9 the set o QlikView documents used to provide the data to the end user
D
2>>7
2>1
+wo-stage architectre The advanta#es o usin# this approach and havin# a QVD La$er are reuse and consistenc$ This approach promotes re+use %ecause, in deplo$ments where multiple documents make use o the same source data, the ori#inal data%ase J-ource La$erK is not overloaded with redundant re/uests This approach is mainl$ used when the source data is #ood enou#h to %e included into the QlikView data model with little or no modication Iowever, when maor data transormation is needed, the administration #ets a little mess$ with this architecture since it is not clearl$ dened at which sta#e these transormations take place( D
2>>7
2>2
+hree-stage architectre
D
2>>7
2>
+hree-stage architectre *n this architecture, two additional la$ers are added9 +rans,oration ;a#er and +rans,ored QVD ;a#er The role o these two additional la$ers is to hold all transormations that need to %e perormed upon the source data %eore it can %e inte#rated into the tar#et data model QVDs can also %e reused with this architecture when the data model o two or more QlikView documents re/uire the same source data This also su##ests that all %ase QVDs will ideall$ keep a strai#ht cop$ o the source ta%le, which will optimie the extraction process D
2>>7
2>&
Setting p or environent =ow that we've discussed the advanta#es o usin# the three+sta#e architecture, let's take a moment to set up our !indows older structure ollowin# the descri%ed #uidelines
D
2>>7
2>0
;oading data alread# stored in .ccessin# data alread$ stored in a QlikView data model JQV! QlikView leK rom a separate QlikView document( !e will call this approach Cloning a QlikView data model .ccessin# data rom the same QlikView document in which the data model resides( !e will call this approach Loading from RAM The a%ilit$ o replicatin# the data model o an alread$ created QlikView document and placin# it into another QlikView document without accessin# the ori#inal data source *n technical terms, it's a Binar# load( Bnce the data model is cloned %$ the second QlikView document, it can %e manipulated urther, inte#rated into a %i##er data model or even reduced %$ removin# some o its ta%les or data( D
2>>7
2>
;oading ,ro /M *n some cases, we will need to read the same ta%le more than once in a sin#le script execution This means, /uer$in# the data%ase Jor QVDsK and pullin# data rom it, and then reprocessin# that same data ater the rst read in order to make it ade/uate or our data model -ince the data is %ein# stored in 3.6 ater each /uer$ durin# the script execution, we can use that 3.6+stored data instead o #oin# directl$ to the ori#inal data source( This is accomplished via a esident load The ke$word 3esident can %e likened to the ke$word Hrom in a /uer$( The diference is that the 3esident ke$word is used to reerence the data in 3.6 model D
2>>7
2>7
/ggregating data !hile QlikView shines in dealin# with massive data volumes, sometimes we ust do not need to load ever$thin# at an atomic level one document will have all data with the hi#hest level o detail and another one will have a similar data model %ut with a##re#ated JreducedK ta%les( This wa$, users are %etter served %$ keepin# a %alance %etween perormance and anal$sis needs !hen a##re#atin# data, the rst step is alwa$s to dene which dimension elds will %e let out and which ones will %e kept in the summaried ta%le we should keep in mind how man$ records the ori#inal ta%le has, in order to %e a%le to measure how much reduction we achieved in the summariation D
2>>7
2>;
/ggregation ,nctions
D
2>>7
2><
Sorting ta&les There is one maor condition or the Brder )$ statement to work9 it must %e applied to a Load statement #ettin# data rom a 3esident ta%le, not rom a ta%le le or an$ other source The Brder )$ statement must receive at least one eld name over which the orderin# will %e perormed and, optionall$, the sort order Jeither ascendin# or descendin#K .n example script o an Brder )$ statement at pla$ is9 Load 3e#ion, Date, .mount 3esident -alesTa%le Brder )$ Date asc D
2>>7
21>
+he )eek ,nction The 5eek unction is an inter+record unction that allows us to literall$ peek into previousl$+read records o a ta%le and use its values to evaluate a condition or to afect the active record The unction takes one mandator$ parameter, the eld name into which we will RpeekR, and two optional parameters, a row reerence and the ta%le in which the eld is located Hor example, an expression like9 5eekJ'Date', +2K !e can also add a ta%le name as the third parameter, as in the ollowin# expression9 5eekJ'Date', >, ')ud#et'K D
2>>7
211
Merging ,orces Bn their own, the Brder )$ statement and the 5eek unction are alread$ powerul( =ow, ima#ine what happens when we com%ine %oth o these tools to enhance our input data( *n this section, we will use %oth o these unctions to add a new calculated eld 8ands on Sessions 1.Merging ,orces 2.Sorting the ta&le 3.)eeking previos records
D
2>>7
212
Dealing with slowl# changing . slow chan#in# dimension is one whose values var$ across diensions undened time periods, that is, it can have diferent meanin#s dependin# on the time period context To tackle challen#es like these, we can make use o the *nterval6atch script unction -ince the dimension value is dependent upon a time rame, the %asic concept is that the ke$ eld, throu#h which the dimension is associated with the rest o the data model, must %e composed o %oth the dimension *D and a time element The splittin# o intervals means that one interval+%ased record in a ta%le will %e converted to several element+%ased records D
2>>7
21
0panding the intervals The *nterval6atch unction splits discrete, numeric+%ased, intervals %ased on two inputs 1( . ta%le composed o two elds9 one or the start o the interval and one or the end o the interval 2( . list o values representin# the individual data points into which the intervals will %e split Jthe time elementK, accordin# to their matchin# .ll intervals must %e closed, that is, the$ all must have an end value
D
2>>7
21&
Soe considerations !hen workin# with the *nterval6atch unction, it is important to keep the ollowin# in mind9 This unction is resource heav$, so, dependin# on the sie o the input intervals ta%le and the elements list, $ou should rst consider The intervals mi#ht enclose elements that are not actuall$ needed in the data model -imilarl$, the intervals ta%le should contain uni/ue records, with no duplicates, to save resources( * one interval is present twice, then the *nterval6atch unction will split it twice( Asin# the Distinct ke$word will help us in this matter !hen usin# the intervals ta%le, the elds must %e specied in the correct order9 the start value %eore the end value (
D
2>>7
210
5ncreental loads
1( Extract the source ta%le in its entiret$ 2( Extract onl$ the new and:or modied transactions rom the source ta%le and append those records to the ones we previousl$ saved in our )ase QVDs
The process o perormin# an incremental load in QlikView varies in complexit$ dependin# on the nature o the source ta%le The %asic re/uirement or an incremental load to %e possi%le is that the new or updated records in the source ta%le can %e identied source ta%le has a 6odicationTime stores the correspondin# timestamp or date or each record( This is oten the case in production environments, %ut sometimes this eld is not availa%le( 21
5ncreental ;oad process !hen records can %e deleted rom the source ta%le, the complexit$ o the incremental load increases and additional steps mi#ht %e re/uired in the process Bne approach that can %e implemented, is to perorm a second load rom the source data%ase, this time pullin# the entire list o record *Ds Jprimar$ ke$sK, without the rest o the elds, and then perorm an *nner Foin operation with the updated ta%le Jthe one resultin# rom the second step in the earlier processK to discard deleted records %eore savin# the new QVD le Iavin# an incremental load lo#ic in our Extract Layer can help reduce the amount o data %ein# transerred over the network rom server to server durin# the extraction process D
2>>7
217
Modle eview and +akeawa#s we have learned the importance o havin# a well+desi#ned data architecture, how to load data rom another QlikView document or previousl$ loaded ta%le in 3.6( Data a##re#ation unctions and their uses( !e then learned how to order ta%les durin# load, how to calculate elds %ased on previousl$ read records( Iow to deal with slowl$ chan#in# dimensions to incorporate those ta%les into the associative data model, and nall$ the #eneral process to perorm an incremental load(
D
2>>7
21;
Modle 13 More on Visal Design and $ser 0perience /,ter copleting this4 #o will &e a&le to know -ome %est practices or creatin# a consistent user interace in QlikView Iow we can add additional interactivit$ to our documents
21<
5n +his Modle6..
2>>7
221
Back Grond 5age !hen settin# up a %ack#round ima#e, either at the Docent )roperties or Sheet )roperties level, it is important to con#ure the ollowin# settin#s 5age =oratting should %e set to :o Stretch 8oriontal should %e set to ;e,t Vertical should %e set to +op Backgrond
D
2>>7
222
+hees . theme is stored in an external G6L le with a (/vt extension( *t contains separate sections or each t$pe o o%ect, document, sheet, and the various sheet o%ects o %ects . theme is created usin# the +hee Maker Wiard and can contain settin#s or as man$ m an$ or ew o%ects as $ou want
D
2>>7
22
/ppl#ing thees .s theme les store properties at various levels Jdocument, sheet, and o%ectK, we can appl$ them at various levels too !e can appl$ a theme to a sin#le sheet, %$ selectin# Settings Sheet )roperties and clickin# on the /ppl# +hee %utton on the General ta% !e can also appl$ a theme to a sin#le o%ect Jor a #roup o o%ects, i we rst select themK %$ openin# the &Fect )roperties window . theme is alwa$s a lwa$s created %$ cop$in# the properties o documents, sheets and sheet o%ects which were alread$ created 3eset the deault theme or the document %$ selectin# the Settings Docent properties rom the menu 22&
/dditional interactivit# )esides properl$ st$lin# $our QlikView documents, how $our document responds to user interaction is also critical to ensurin# a pleasant user experience )$ usin# triggers, QlikView lets us respond to certain events Jor example, when a sheet is selectedK with an action( Tri##ers can %e dened at various levels .t the document level Jwhich can %e opened %$ selectin# Settings Docent )roperties +riggersK, we can set Docent, =ield and Varia&le 0vent +riggers =ot all tri##ers and actions are supported when deplo$in# $our document to QlikView -erver D
2>>7
220
Sheet triggers
D
2>>7
22
/ctions .ctions can %e attached to the ollowin# o%ects 1( )uttons 2( Text B%ects ( Nau#es &( Line:.rrow B%ects Hrom the /ctions dialo# window we can add an action %$ clickin# on the /dd %utton( Deletin# an action is done %$ clickin# the Delete %utton( The )roote and Deote %uttons are used to chan#e the se/uence in which the actions are executed !hen we click on the /dd %utton, we are taken to the /dd /ction dialo# window( *n this menu we can select an action rom a ew select /ction +#pes D
2>>7
227
/dvanced search epressions -ometimes we need our searches to %e a little more ne+#rained( This is where advanced search epressions are useul .dvanced search expressions are useul not onl$ or ad hoc searches( !hen we create a %ookmark %ased on an advanced search expression, the resultin# %ookmark will %e d$namic
/lerts !ant to %e alerted when a certain threshold is exceeded( This is where alerts are useul .n alert can %e tri##ered %$ three events9 ater the document is opened, ater it is reloaded, or ater data reduction( The alert can show a pop+up messa#e or send an e+mail to one or more recipients( D
2>>7
22;
2>>7
22<
Modle eviews and +ake awa#s we have learned how to create a consistent QlikView A* %$ usin# a xed screen resolution, %ack#round ima#e, and themes !e also learned how to add additional interactivit$ to our documents %$ usin# tri##ers, actions, advanced search expressions, d$namic %ookmarks, alerts, calculation, and show conditions
D
2>>7
21
Modle 1! - Secrit# /,ter copleting this odle4 #o will &e a&le to know Iow to create a hidden script that is onl$ accessi%le to a select #roup o developers Iow to allow onl$ authoried users to open $our document Iow to limit what a user can do and see within $our document
22
5n +his Modle 8idden script Section access Docent-level secrit# Sheet-level secrit#
D
2>>7
2
8idden Script !hen QlikView script is %ein# executed, the results o the actions are written to the Script 0ection )rogress window Jand, i ena%led, the lo# leK -ometimes $ou do not even want all developers to have access to the entire script( This is where the hidden script comes into pla$ The hidden script is a password protected part o the script( *t is alwa$s the let+most ta% Jand cannot %e movedK, so it is executed %eore the re#ular script is reloaded( .n$thin# that is executed within the hidden script is not written to the lo# Lo##in# or the hidden script can %e turned on %$ checkin# the Show )rogress ,or 8idden Script check%ox on the Secrit# ta% o the Docent )roperties D
2>>7
2&
Section access -ettin# up user authoriation under QlikView is #enerall$ reerred to as section access, named ater the statement that initiates the authoriation section o the script *n section access, elds are loaded with details on which user is allowed which access ri#hts( These elds are loaded in the same wa$ as an$ other eld in QlikView and can %e sourced rom an inline ta%le, data%ase, or external le )esides usin# an inline ta%le, data%ase, or external le, there is also the option o storin# and maintainin# section access inormation under QlikView 5u%lisher *t is stron#l$ recommended to make a %ackup cop$ o $our QlikView document %eore settin# up section access 20
Section access Aelds we used the .CCE--, A-E3*D, and 5.--!B3D elds, %ut as we saw in the /ccess estriction +a&le Wiard dialo#, there are more options, as seen here9
2
Docent-level secrit#
Ctrl ? Alt ? D to open the Docent )roperties dialo# and selectin# the Secrit# ta%
D
2>>7
27
Sheet-level secrit# %$ selectin# Settings Sheet )roperties in the menu and selectin# the Secrit# ta%
2;
Sheet level - $ser privilege
2<
!e learned which users #et access our document( !e then Modle eviews andto+akeawa#s looked at how we can restrict the data that diferent users have access to( !e learned how we can set user privile#es at the document and sheet levels( !e learned how to create a hidden script and also that it is ver$ important to create a %ackup %eore introducin# -ection .ccess( !e also learned how to add -ection .ccess to $our document and how to identi$ users on diferent criteria we learned how we can use -ection .ccess to d$namicall$ reduce the data that is availa%le to the user and how to set user privile#es at the document and sheet levels( D
2>>7
2&>
Modle 1" Macros and /toation /,ter copleting this odle4 #o will &e a&le to know The QlikView .utomation *nterace Iow .utomation and 6acros Can Control QlikView *nternal 6acro *nterpreter
D
2>>7
2&1
/toation and Macros with QlikView QlikView is e/uipped with an .utomation interace( This interace allows an external pro#ram pro#ram or internal macro to access and control the QlikView application The .utomation inerace is an inte#ral part o QlikView QlikView and $ou do not have to perorm an$ special tasks to activate it QlikView o%ects are accessi%le %$ means o .utomation rom external pro#rams, pro#rams, e(#( pro#rams written in Visual )asic or C? ? supportin# .utomation QlikView o%ects are also accessi%le via .utomation rom inside QlikView %$ means o the %uilt+in macro interpreter( interpreter( 6acros written in V)-cript or F-cript inside a QlikView Q likView document can currentl$ curr entl$ %e invok invoked ed in several wa$s throu#h events D
2>>7
2&2
0vents Docent events Sheet events Sheet o&Fect events Btton events =ield events Varia&le events 2&
Macro 0ditor *rrespective o how the$ are tri##ered, macros are created and edited in the Edit 6odule V)-cript unctions *nput)ox and 6s#)ox can %e used reel$ in V)-cript macros, no direct counterpart is availa%le when usin# Fscript( Hor this purpose a special li%rar$ /vli% has %een added or these actions 6acros and custom dened unctions can %e written in V)-cript or F-cript usin# the 0dit Modle dialo#( The module is saved with the document The Uer !reference" Editor dialo# is opened %$ choosin# the
Macro 0ditor
D
2>>7
2&0
Macro De&gger
D
2>>7
2&
