This is an application to Scan and Monitor - Qlik server folders (Windows). 

This application gives precise details about the changes happening in the files of windows folders over the period of time with help of snapshots taken at each refresh of this application.

This application gives below Key features about files in windows folders:

1. File Size (Displayed in KB/MB/GB)
2. File Age
3. File created time
4. Newly created files (between previous refresh and latest refresh of this application)
5. Removed files (between previous refresh and latest refresh of this application)
6. Files those are updated - File sizes are modified (between previous refresh and latest refresh of this application)
7. Volume trend of the folder over a period of time (With help of Snapshots)
8. Meta data of  available QVDs
9. Categorizes first 5 Levels  of folder within the input scanned folder.

This application is just plug and play with below mandatory steps (to be done in Configuration tab). -

1. Input the directory path that needs to be analyzed. 
2. Create and Input couple paths in your system to allow this application to store snapshots.

Tips - Where to use:

1. Qlik Admin purposes - Configure this application to scan Qlik documents folder in the server and schedule it to refresh in  a good interval of time. You can easily spot if there is an unexpected volume growth in any of the applications. Also will be helpful to spot  creator document of the QVDs. In an active environment, when no longer the inactive QVD/QVW are required, they can be spotted with the help of Age Category  and can be removed from the server.
2. General - Analyze any windows server and monitoring for Changes happening with in the files.
3. General - To spot and clear old unused files, for example - files aged more than 180 days with no updates. (Make sure if files are in use or not, though there are no updates to them)

Hi Community,

I would like to share with you something which I thing can be very useful.

Few month back i work on Complex Authorization in QlikView for very complex Row Level security. During implementation it was too difficult to create various possible combinations if we have more number of columns.

For example see below security combination where for user TEST2, we you want to reduce the data as per below logic i.e. ([Dim 1]=’A’ AND [Dim 2]=’C’) OR ([Dim 1]=’B’ AND [Dim 2]=’F’)

In order to archived this we need to create all possible combinations into the Security Bridge table which connect Section Access table & Main data-set on which security need to apply.

But if you have too lot of fields then creating those combinations is too hard. In order to overcome this situation i have written script in QVW which generate this all combinations based on # of fields you supply in configuration.

In Configuration Details, need to specify the parameters values. Once all parameters are pass then you need to Reload the QVW, which will automatically generate script and you can copy the script from Generated Section Access Script section. In Configuration Details all parameters are required

After copying the script, go to STEP 5 where you need to modify the details as per your details & for STEP 6 you need to enable SECTION ACCESS & SECTION APPLICATION lines.

If you have any doubts feel free to ask.

Thanks,
Akshaye

Long time ago I have developed a vbscript application to open qlikview, reduce some data according salesman filter, export the sales pivot table to excel and send an email with the excel sheet attached to the salesman using the blat.exe  application. The other day i was wondering if the same job could be executed using python. So after two minutes i was googling in search of some code snippets to easily make the python script.  After half an hour I was quite disappointed to not found anything already made so I opened Pycharm and started to make the basic code: a class to make an instance of qlikview, open the document, export the table, close the document, close the qlikview app.

Here the code tested on python 3.7:

from win32com.client import Dispatch
import pythoncom

class QlikView:
def __init__(self):
self.app = Dispatch('QlikTech.QlikView')

def OpenDoc(self, docname):
doc = self.app.OpenDoc(docname)
return doc

def CloseDoc(self, doc):
doc.CloseDoc()

def ManageDocument():
docname = "c:\EXAMPLE.qvw"
q = QlikView()
version = q.app.QvVersion()
print(version)
doc = q.OpenDoc(docname)
chart = doc.GetSheetObject("exampletable")
chart.ExportBiff("c:\exampletable.xls"

 q.CloseDoc(doc)
 q.app.Quit()

if __name__ == '__main__':
ManageDocument()

This is the code that I've written at today , for sure i will go on and rewrite all the app to send report attached in emails, if  someone need more information, just send me a message.

Note: This document was originally written by Rakesh Mehta, I've just cleaned it up a bit...

Hi Guys, I saw this question asked many times in the forums so thought I would do a little write up on it so all can take little benifit of the trick.

In my example I will use a freely available webservice from http://www.cdyne.com/ called - weather forecast webservice. The base URL for this webservice is http://wsf.cdyne.com/WeatherWS/Weather.asmx. The method I will use in my explaination is "GetCityWeatherByZIP" method. This method returns weather forecast of next 7 days for a given US zip code.

We all know that one way to get data consuming a webservice is by building a Custom Data DLL interface (eg salesforce example from QlikTech), which werks perfectly but surely requires extra programming abilities like in C++, etc.

What I have found that a webservice can also be consumed (or invoked) using 3 ways:

• A POST command that embeds a SOAP request
• A POST command that specifies the method name and parameters
• A GET command whose URL contains the method name and parameters

In my example, I have used the third method - A GET command whose URL contains the method name and parameters. Below is how I programmed it:

1. Call the URL http://wsf.cdyne.com/WeatherWS/Weather.asmx?op=GetCityWeatherByZIP in Internet Explorer, and put a valid US zip code. Press "Invoke" button. This will return you an XML file with weather forecast of that zip code of next 7 days. Copy all the xml data (CNTL-A + Copy) and Paste it in a notepad session. Save this file as temp.xml somewhere on your PC.
2. Open a new QVW file, and in the script editor press "XLM Files" button. Locate the file you saved in step 1 and go through the wizard. This will put a script to load the data. Go ahead and remove the path from the file name, leaving just the file name. By doing 1 & 2 steps, now you have a valid load statement with correct table and field names.
3. Open VB Macro script editor and Place this script:

   Sub ws
    Const HOST = "http://wsf.cdyne.com/WeatherWS/"
    Const URL = "Weather.asmx"
   
   'Create xmlhttp opject
    Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
   
   'Get the selected zip code
    set fld=ActiveDocument.GetField("ZipCode")
   
   'If user has selected more than one zip code, show error
    if fld.GetValueCount(1) <> 1 then
    msgbox ("Please select only one Zip code")
    exit sub
    end if
   
   'Finally, get the selected zip code value
    set val=ActiveDocument.Fields("ZipCode").GetPossibleValues
    zip = trim(val.Item(0).Text)
   
   'Call HTTP Get method passing WS URL and parameters
    xmlhttp.open "GET", _
    HOST & URL & "/GetCityForecastByZIP?ZIP=" & zip, false
   
   xmlhttp.send ""
   
   'Create a temporary file to save the results from WS
    set Path = ActiveDocument.Variables("vPath")
    FileName = Path.GetContent.String & "XMLOutput.xml"
    set fso = CreateObject("Scripting.FileSystemObject")
    set s = fso.CreateTextFile(FileName, True)
    s.writeline(xmlhttp.responseText)
    s.Close()
   
   'Load document with the temp file
    ActiveDocument.PartialReload
   
   'Delete the temp file
    fso.DeleteFile(FileName)
    end sub
   

4. Now you need to make a way to load all possible US zip codes in the document so the user can select a zip code. I have loaded list of zip code from a URL I found while googling - http://www.census.gov/tiger/tms/gazetteer/zips.txt . You can always put an input box and let user type a zip code, and then move this zip code in a variable and then use that variable in the VBScript to pass to the WS URL.That's it. Try it with the example QVW file I have put in "Share QlikViews" section. Before you press button to get data, please make sure to give proper system access in the VB script section for macros to run through.

Now, would I use this method for importing lots of data from the internet? May be not. For that I would rather build a custom DLL. But to get little things, like current stock quote, or some currency exchange rates, etc. I think this method can be utilized.

Anyway, something to share. Have fun! Comments / suggestions are welcome.

Rakesh Mehta

In this Document I am going to demonstrate a requirement and how to implement the same.

The Requirement:-

First we will get an overview of the application.

I have an application with section access for data security.

The Section access table is shown below.

Now consider that the application is up and running as required………

1.The user Ray is able to see data for all regions and for all branches under that region.

2.The user Nadeem is able to see data for IND region and the branch DEL under that region.

3.The user Darren is able to see data for IND region and the branch MUM under that region.

All the above users are managers having access to data for their respective branches.

Now out of the blue I have a new requirement to build a scorecard sheet.

This scorecard sheet should display data for the branch manager of that particular branch as well as data of all other branches .

Eg:- When the user Nadeem Logs on he should be able to see data for DEL branch along with all other branches. Similar would be the case for the user Darren.

In order to meet the above requirement I should somehow defy the section access logic already built.

Note:- You would think that I could simply remove section access or change my section access BRANCH_NAME fields to ALL . But I still need the section access for the rest of my sheets in the application, It is just the scorecard sheet where this twist has arrived (hence the title section access with a twist .)

How to meet the requirement:-

1. First we will have to identify the tables that would be used to create the scorecard KPI.

Let us consider that we have 2 tables and their names are

                                a. Enquiry

                                b. Enquiry_Target

1. 2.These two tables are currently already loaded in the application.

Now we will again load these tables and use different field names

Eg

the table Enquiry had the following fields in the data model originally

Region,

Branch_name,

Client_city,

Enquiry_id

Reload the tables and rename all the fields as S.branch_name, S.Client_city, S.Enquiry_id except the field region.Similarly perform the operation for the second

Renaming these fields will cause section access to ignore any charts created using these fields in the application.

Now if we use any of the renamed fields like S.Branch_Name in our charts Section acces will not have any effect and all the branches would be displayed because in our section access configuration file we have used field name Branch_name.

Note: We have not renamed Region field because we want section access to only ignore the branch field in the scorecard sheet.

The above screenshot shows that when the user Nadeem logged in, he had access to only DEL branch data hence u see only DEL in the Branch_name list box on the extreme left.

But when the field S.Branch_name is used the user Nadeem can see all the data irrespective of the section access defined for the application.

The goal of this requirement was to see where a particular Branch Manager stands and where other branch managers stand so as to fire up a competition between branches.

Thus our Goal is achieved.

Hope you liked the Content

Build a complete QlikView 11 application step-by-step: a QlikView 11 tutorial for developers

This QlikView 11 tutorial is for developers with at least some programming experience in any computer language. Over the course of the tutorial, you will learn how to:

• read data from multiple sources (data from websites, data from Excel, data from databases),
• cleanse data,
• create a fully featured data model without circular references,
• display data in maps, charts, and text across multiple sheets.

This QlikView tutorial will teach you a range of methods that QlikView developers use to create fully featured professional quality applications.

You can follow the tutorial step-by-step, or you can jump in and just view part of it. The steps are designed to be independent of one another and also to work as short briefings on developer topics (e.g. connecting to a database with an ODBC connector).

For each step of the tutorial, we have made available a video, the video script, the QlikView file, and the data files (where appropriate). You can download all of these items free of charge by clicking on the download links.

Here's what's in the tutorial:

This page is part 1 of the tutorial.

Going beyond this tutorial, the resources on our education and training pages and on QlikCommunity will be helpful to you.

Get the script for the videos

Sometimes, videos aren't enough. If English isn't your first language or you have hearing issues, it can be hard to follow what's said during videos. To help you out, we've written out the full script for the videos. You can download the full video script.

Overview

Here we provide an overview of the tutorial and explain how to get the project files up and running.

Watch the video at full resolution

Download and use the files

The files for this part of the tutorial are complete - but they are large. They include all of the database files which will consume several GB on your hard drive and may take a while to download, depending on your internet connection.

Here are the files to download:

• The QVW file - warning - this is a large file
• The images used in the demo
• The census files used in the demo
• The SVG files used in the demo
• The database files - warning - this is a large file
• The QVD files (optional) - warning - this is a large file

How to use the downloaded files:

1. Unzip the QVW file to a folder.
2. Unzip the image files to an Images sub folder (under the QVW folder).
3. Unzip the census files to a Census sub folder (under the QVW folder)
4. Unzip the SVG files to a svgMap folder (under the QVW folder)
5. Unzip the database files to FARSDBF folder (under the QVW folder)
6. Unzip the QVD files to a QVD folder (under the QVW folder).

When you're done, your folder structure should look like this:

To reload the data, you may need to edit the load script to point the script to the correct file locations. The script uses file locations in several places, but they are clearly marked. You'll also need to connect an ODBC database to the DBF files. How to do this is covered in the 'data from databases' section.

What's next?

Education and training

A short tutorial can only cover a few topics. To fully exploit the full power of the QlikView platform, you should consider taking a QlikView course.

We offer a range of courses for different needs, ranging from courses for UI designers through to courses for developers and system administrators.Courses are available in-person and virtually. We even offer several courses you can take at no charge.

To find out more about our training courses, visit this web page: http://www.qlikview.com/us/services/training

QlikCommunity

Very few problems developers have are unique. If you have a QlikView problem, there's a good chance someone else has faced the same problem and solved it. QlikCommunity brings together the global QlikView community into an open forum where community members ask and solve questions, share advice,and provide example applications. Joining the community is free.

To join the community, sign up here: http://community.qlikview.com/welcome

The attached archive contains instructions and per-requisite files to enable SSL on QlikView Webserver (11.2).

The core document was based upon an earlier document for QlikView 9/10,  I have made some modifications (mainly in how the certificate is created, IIS does not need to be installed in this scenario).

Hope this helps,@

Clint

Hello.

As far as I was designing apps for QV - I've found a great need in some charts to ignore all possible selections except some specific fields.

Exploring possible ways to do so - the first my decision was to use dollar-sign expression inside my Set Analysis which could make an exception like next:
(Let's imagine that in my scheme there are fields [Stock], [Manager], [Date], [City], [Client] and [Amount])

sum({1<[Stock]={'Stock 1','Stock 3'}>} Amount)

If I selected only 'Stock 1' and 'Stock 3' in field [Stock] - and this way has shown it's profit - you can get it using this expression:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', ''))>} Amount)

If you need two, three, or more fields - you can simply add such code for every field into Set Analysis:

sum({1<[Stock]=$(=if(GetSelectedCount([Stock], true( ))>0,'{' & chr(39) & GetFieldSelections([Stock],chr(39) & ',' & chr(39),GetSelectedCount([Stock], true( ))) & chr(39) & '}', '')),[Manager]=$(=if(GetSelectedCount([Manager], true( ))>0,'{' & chr(39) & GetFieldSelections([Manager],chr(39) & ',' & chr(39),GetSelectedCount([Manager], true( ))) & chr(39) & '}', ''))>} Amount)

But what if you have a field containing hundreds of records? Can you imagine what will be the length of your expression? That's why I started to discover another way to do that. I've thought - what if I use not "1" Set, but "$" Set in my expression and write in it all the fields that must be ignored, something like that:

sum({$<[Date]=,[City]=,[Client]=,[Amount]=>} Amount)

So, using such expression - I get that all the fields except for [Stock] and [Manager] are ignored, but how car I make it automatic? Sure using our lovely dollar-sign expressions and system field $Field:

sum({$<[$(=Concat({1<$Field-={'Stock','Manager'}>}distinct $Field,']=,[')&']=')>} Amount)

where inside the set analysis of Concat function you can insert fields that you are interested in (by the way - there you can insert even tables using system field $Table and others).

Hi,

As a Developer/Consultant, time and time again I would hop on my client's/customer's server (after receiving requests for modifications to reports) and then wonder:

• Am I 'ok' to open up a QlikView file?
• Can I restart the QlikView Server Service to release Memory?
• Can I reload and deploy my updated QlikView file?
• etc. 

I bring up these questions because a lot of times when I go to do development work on the client's server... I may potentially 1) crash the server, or 2) kick users who are running (important) reports out (off the Access Point)... Obviously there are other potential risks, but even these two points alone can be a big no-no... Nobody wants their employees' work to get halted because some developer crashed their server.

So this is my basic guide on knowing when it's ok to do these things, and when it's not...

Step 1) After logging into the server, check Memory in Task Manager... If you know the qvw file you're about to work on is large and can severely impact memory... Do NOT open the file if memory is already pretty high (i.e. the below image's memory is okay... but not what I like... and it really depends on how much memory the server has, and how big the file is, etc.... but I usually avoid opening qvw's during a work day when memory is around 70% of the total... I like to stick to opening the file when it's less than 20%)

Step 2) Check Qlik Management Console for Active Sessions... If no documents are open/no active users... this is usually a good time to restart the QlikView Server service,  open the qvw, make changes, and do a reload (if needed)... see below screenshot example of QMC when no documents are open/no active users. (I blacked out the server name and file path for security purposes... but the main point is there are 0 Sessions for this document... so now is a decent time to work)

Step 3) Like I mentioned above, if no active sessions are going on, and memory isn't the best... it's a good idea to restart the QlikView Server service (just type in Services in the start menu search box to open up the list of services, and find QlikView Server)... this will drop the memory and help prevent any server crashes when you're about to open large qvw files (image of QlikView Server service below)

Step 4) If all of the above steps have been checked/completed... usually you are okay to open up a local copy of the qvw on the server, and make any changes or do a reload... once changes/reload is complete and the file is ready for deployment, I almost always check with the client if they're ready for me to swap in the updated file for the live file, unless they specifically said I could do so previously, or if they told me to place it in a certain location so they could do it their self (but ALWAYS create a backup of the live file... never hurts).

And that's about it... This is just a general guide I've learned/taught myself, so please feel free to add any comments you think would be useful, or if you think anything I've said was misleading/incorrect, and I will update as needed.

Thanks,

Ben

The  attached file SectionAccess.zip contains several example documents that show how Section Access can be used to restrict access to specific users and to specific data.

This is not a guide on how to use Section Access, but instead shows some of the common errors and how to avoid them.

An excellent guide to Section Access can be found here http://community.qlik.com/docs/DOC-1853

Marcus_Sommer has accumulated a comprehensive list of Section Access Resources in this document Section Access

These examples will require a licenced copy of QlikView to open them, so they not suitable for users with QlikView Personal Edition.

The example documents will ask for a username and password to open them and set the Section Access permissions. The following usernames and passwords can be used to open the documents and see the different functionality with Section Access. When changing from one user to another, you must close QlikView before you can open a document as another user.

UserID,  Password

ADMIN,  ADMIN

JOHN, JOHN

MARY,  MARY

PETER,  PETER

SIMON,  SIMON

You can view the load script to see how the Section Access and data is configured.

Section Access Example 1

This document attempt to restrict access to a list of named users, but this fails as the section access field names are not set as UPPERCASE in the script.

Section Access Example 2

The app is now correct and prompts for a username and password before users can access the data.

Examples 1 & 2 use the Basic section access data from SectionAccess.xlsx

Access,  UserID,  Password

USER,  USER,  USER

ADMIN,  ADMIN,  ADMIN

USER,  JOHN,  JOHN

USER,  MARY,  MARY

USER,  PETER,  PETER

ADMIN,  SIMON,  SIMON

Section Access Example 3

The data is restricted so users can only access the Cost Centre data as listed in the Cost Centre  column in Section Access.

Access,  UserID,  Password,  CostCentre

USER,  USER,  USER,  *

ADMIN,  ADMIN,  ADMIN,  *

USER,  JOHN,  JOHN,  A

USER,  MARY,  MARY,  B

USER,  PETER,  PETER,  C

ADMIN,  SIMON,  SIMON,  A

Section Access Example 4

This adds extra DUMMY rows to the section access table so the * entries can now access all cost centres.

Access,  UserID,  Password,  CostCentre

USER,  USER,  USER,  *

ADMIN,  ADMIN,  ADMIN,  *

USER,  JOHN,  JOHN,  A

USER,  MARY,  MARY,  B

USER,  PETER,  PETER,  C

ADMIN,  SIMON,  SIMON,  A

DUMMY,  DUMMY,  DUMMY,  D

DUMMY,  DUMMY,  DUMMY,  E

DUMMY,  DUMMY,  DUMMY,  F

DUMMY,  DUMMY,  DUMMY,  G

DUMMY,  DUMMY,  DUMMY,  H

Note – you cannot open the document as user DUMMY because DUMMY is an invalid Access value, only ADMIN and USER entries are allowed to open the document.

Section Access Example 5

This adds Region to the restrictions as well as CostCentre, but the restriction on Region fails as the

region fieldname is not UPPERCASE.

Access,  UserID,  Password,  CostCentre,  Region

USER,  USER,  USER,  *,  *

ADMIN,  ADMIN,  ADMIN,  *,  *

USER,  JOHN,  JOHN,  A,  North

USER,  MARY,  MARY,  B,  South

USER,  PETER,  PETER,  C,  East

ADMIN,  SIMON,  SIMON,  A,  North

ADMIN,  SIMON,  SIMON,  A,  East

DUMMY,  DUMMY,  DUMMY,  D,  North

DUMMY,  DUMMY,  DUMMY,  E,  South

DUMMY,  DUMMY,  DUMMY,  F,  East

DUMMY,  DUMMY,  DUMMY,  G,  West

DUMMY,  DUMMY,  DUMMY,  H

Section Access Example 6

The Region fieldname is uppercase, but the restriction on Region still fails as the region data is not UPPERCASE.

Section Access Example 7

The Region restriction now works by loading the region data into a second field named REGION_SA and forcing the data in REGION_SA to UPPERCASE.

Version3 - The Region field has been removed from the Section Access table. This fixes the error identified by kanhomcake (March 27 2014).

Hopefully this will help avoid some of the basic errors that can be made when implementing Section Access.

Colin Albert.

What is Control Chart?

The control chart is a statistical analysis used to study how a process changes over time. Data are plotted in time order. Control charts are used to routinely monitor quality or simply you can say, control chart is process improvement technique.

What is a process?

A process is simply what we do. It can be filling out an expense report, checking a person into a hospital, burning a CD with music, driving to work, filling a prescription, etc. All these process generates the data. We take these generated data and plot on control chart to improve efficiency.

There are many types of control chart, but we are going to use the basic control chart with Std dev method to understand the concept.

Followings are some scenario where we can use the control chart

1) In manufacturing to test if machinery are producing products within specifications.

2) suppose you want to reduce the time it takes to admit someone to the hospital.

3) If you want to know how views are going up or down for particular website.

Components of Control Chat

1) Avg line

2) UCL (Upper Control line) = Avg Value + Std Deviation* Std Dev Multiplier

3) LCL (Lower control line) = Avg Value - Std Deviation* Std Dev Multiplier

So, basically we can see which are the points "Out of Control" or "In Control".

You can control your Std Dev multiplier using variable.

How to create the Control Chart.

1) Create the Combo Chart.

2) Take any Time dimension of your interest which is suited for your analysis, here I have taken the MonthYear. Also    make it Continuous in Axis setting

3) Create two Expression, one for Line and second for Symbol.

4) Create expressions for Avg line, UCL & LCL which will be used as reference lines in the chart. Check the Chart properties ->Presentation for reference line setting.

5) Create the Background color setting for Symbol expression

if( sum(Sales) < vUCL and sum(Sales) > vLCL, LightGreen(), LightRed())

6) Finally the chart will look like below

There are some other useful links available to create the control charts

QlikView Control (SPC) Chart

See also :

Benford's law

Bollinger Bands

(Taken from my blog - QlikCentral.com)

I parked the car in Leeds on Monday the 7th of April 2014 and headed to the testing center.

I was on my way to my QlikView 11 Developer and Designer Certification exam and I was a little anxious. I was trying to remember the last time I had to take an exam. Not counting the occasional interview test it must have been 15 years ago (my driving test). I felt confident with using QlikView although the possibility of ‘fluffing’ the exam worried me. One question could mean the difference between disappointment and jubilation.

In my mind I expected to arrive and great several other QlikView developers waiting for the same test, feeling similar feelings, and swapping QlikView stories. That wasn’t the case. I was checked-in in a small reception area, signed forms, had photos taken and had to leave all possession’s in a small locker… Nothing was allowed in to the test area, not even a watch or a few coins in my pocket. “Those are the rules”, I was informed. “It’s the same everywhere and this is a world wide test.”

I was led into a small room with a bank of PC’s along each wall leaving a small walkway down the center. Each PC station had a divider at each side so once seated you be effectively isolated. There was one chap at the far end of the room as I entered and he left before I’d finished, I never spoke or properly saw him and I’m not sure if he was there for the same exam.

I took another quick look around. Camera’s were positioned high on the walls at regular intervals. I’d been left a single laminated sheet of paper and marker pen to write notes with. The computer was set ready from me to begin. After reading the welcome text I clicked forward into the exam.

The test was 70 questions long and I had 2 hours to complete them all. My first calculation was how many questions could I get wrong and still pass, not many as it turns out.

Both the time and the questions number were displayed in the top right hand corner of the screen and during the test I calculated if I was on track or if I was being too slow and needed to pick up the pace

.

Questions were multiple choice with normally four to choose from. Occasionally there were more and I was asked to pick two correct statements, I read later there was no partial credit for these and I also discovered you weren’t marked down for incorrect answers.

Working through the questions found a mix that is usually expected. Some were easy, most were challenging, and some were very difficult. All the questions were either you knew them or you didn’t (hence the difficult ones). All of the questions had three very plausible but wrong answers so I really had to check the syntax and the question working it through my mind and trying to visualize how it would work practically if I were back at my desk at work.

I soon implemented a tactic. For the easy questions or the ones I was confident with I’d simply move on. For all the others I would select the answer I believed to be correct and ‘flagged’ the question for review. This seemed to work well and I’d completed the 70 questions with 45 minutes to go which left plenty of time to review the ones I’d flagged.

15 minutes later and I’d arrived at a point where I could do no more. I didn’t want to start swapping answers to questions I wasn’t 100% confident about between the two I thought could be correct. After all I could be making things worse. So I took a deep breath and I ended the exam with 30 minutes left on the clock.

It asked me to confirm…. It asked me to confirm again.

The results flashed onto the screen and I quickly began scanning the text. I saw my mark was over the 80% required, Yeh! (although on further reading I discover 75% was required to pass). I stopped holding my breath and smiled, not that anyone could see me unless they were monitoring the camera feed.

I left the room, picked up my belongings from the locker and made my way back to my car. Six hours later I had an email to say my certificates were ready to be printed.

Broad experience in using QlikView is key to acquiring certification. At least one years experience and ideally more in a dynamic environment where you’re constantly developing new coding skills and incorporating as many functions into your QlikView application as possible.

For instance; Class, Dual and Apply Map may be the staple of some QlikView developers where as others may have never come across them before. Some developers may have the luxury of very well designed or simple data sources and others have to learn how deal with complex data structures.

Preparation could include reading up on areas of QlikView you have little or no experience in. For example perhaps you don’t have (or use) QV Publisher, maybe you have never developed a mobile app. Having some theoretical knowledge in all areas even if you don’t expect to use it practically in the near future will help when taking the exam.

I hope this post may be of some help to people who are preparing to take the certification exam or perhaps you already have. Either way I’d love to hear your comments.

Good Luck

Richard

Hi,

Recently I helped a community member to calculate the Trimean, I found this indicator interesting, so I decided to share it here as well.

The trimean (TM), or Tukey's trimean, is a measure of a probability distribution's location defined as a weighted average of the distribution's median and its two quartiles: (1)

trimean calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. You can use this function when you wish to exclude outlying data from your analysis (2)

Example :

for the following example :TRIMMEAN is 3.778

LOAD * INLINE [
    Data
    4
    5
    6
    7
    2
    3
    4
    5
    1
    2
    3
];

To calculate Trimean on Qlikview, we will take the example below :

the expected output is :

the script can be presented as :

Data0:
LOAD 
	 DATE, 
     [CLAIM PAID]
    
FROM
[.\TRIMMEAN(1).xlsx]
(ooxml, embedded labels, table is Sheet1);

Data:
noconcatenate
LOAD rowno() as IDtmp,
	 DATE, 
     [CLAIM PAID]
    
resident Data0 order by [CLAIM PAID];
drop table Data0;

let percent=0.1;

Tmp1:

load count([CLAIM PAID]) as CountClaim resident Data;

let vcount=peek('CountClaim',0,'tmp');

drop table Tmp1;

let k=floor($(vcount)*($(percent))/2);

Tmp2:
load min(IDtmp) as minIDtmp, max(IDtmp) as maxIDtmp resident Data;


let Idmin=peek('minIDtmp',0,'Tmp2')+$(k);
let Idmax=peek('maxIDtmp',0,'Tmp2')-$(k);

drop table Tmp2;

output :

I am trying to export data from a QlikView Chart/Table in the Access Point or QlikView Desktop and I am receiving the below message:

'Microsoft Excel cannot open or save any more documents because there is not enough memory or space available'

When looking at memory and disk space, everything looks fine.  Not sure why this is happening.  I am trying to do this on Windows Server 2012 R2 with Excel 2013.

Working hours calculation is essential measure which is useful in many business cases.  In this post I am going to demonstrate the steps to calculate this measure.  I have also covered other aspects related to working hours like overtime hours and excluding lunch break hours.  

Example in this post works for weekends as Saturday and Sunday. If you have any other weekends then refer to my below post

Working-days-and-hours-calculations-for-custom-weekends

Consider the below case

Let's say one Ticket is logged into the system on certain day , call it as Start Date, and the same Ticket got resolved on certain day, call it as End Day. Now we may want to calculate the working hours between these two Dates to calculate the efficiency of ticket closure time.

1) Calculate business working hours excluding week ends(Saturday and Sunday) and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Holidays

Below is the Setup in QlikView

You can change the Date format of the below variables as per your convenience eg. 'DD/MM/YYYY hh:mm:ss' to 'DD-MMM-YYYY hh:mm:ss'

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

First, convert your Timestamp date format to 24 hour format using the below functions

a) Timestamp# ()    (if your Timestamp values are text) 

b) TimeStamp ()     (If your Timestamp values are in proper Timestamp format)

Now you can setup the below variables for standard working hours in weekdays.You can change the Variable according to your working hours (Here 9 AM - 6 PM) and rest calculation will be done automatically

Now Setup the Holiday list as below. You can use your own holiday list

Below is the logic to calculate the business working hours between to dates

Here is the output

2) Calculate business working hours excluding week ends(Saturday and Sunday), Lunch Breaks and Holidays.

Below are the considerations

1) Count only standard working hours ( 9 AM - 6 PM)

2) Exclude Saturdays and Sundays

3) Exclude Lunch Break (1 PM - 2PM)

4) Exclude Holidays

Set the Variables for standard working hours and lunch breaks. You can change the values according to your needs

Include the Holidays

Below is the logic to calculate the business working hours between two dates

Here is the output

If you want to present the working hours in hh:mm:ss format then you can use below logic after calculating working hours

=interval(Working_Hours/24,'hh:mm:ss')

Please see the attached QVW.

Feel free to provide any suggestions.

What it is used for?

The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’

In situation where we have two (or more) Tables with the same field names:

Product	Payment

The Qualify statement will assign name of the Table to fields:

Remember

• To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Qualify.xlsx]

QUALIFY *;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

The Outcome:

Table Viewer:

Read only if you need to know more....

If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:

The end result  -merge of those two Tables is correct only for the “Serial No” fields

The “Value” and “Category” fields although merged is none of use to us.

To fix this we can only Qualify fields that we do not want to associated:

QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

or by using UNQUALIFY statement:

(which works as opposite to QUALIFY)

QUALIFY *;
UNQUALIFY [Serial No];
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY *;
UNQUALIFY [Serial No];

Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)

Outcome is as below:

Fields:

Table Viewer:

Feeling Qlikngry?

How To /Missing Manual(18 articles)

What is a Mapping Load

Mapping Load is definitely one of those essential tools for your QlikView and Qlik Sense scripting. Similar to a left join, but different in distinct ways that allow you to do cool things in your QlikView scripts that would be difficult to do or would otherwise involve many steps.

Basically a mapping load allows you to “map in” a set of values to a recipient table based on a key field.

We will first look at the properties of these functions and examine the syntax. Then we will review 8 different applications of this to help you understand where these functions are a good fit.

The script below utilizes QlikView in this case, but we can apply these scripting techniques directly to Qlik Sense as well with the exception of the “from” statements which get changed to library connections.

Highlights of Mapping Load and ApplyMap

• Mapping loads come in two parts. First, a mapping table must be created. Then the map can be applied.
• Maps always consist of two fields. The first field is the key field that will be used to make the translation (the ID). The second field contains the values to be mapped.
• Once a map is created, it is held in memory until the script finishes and is then dropped. It can be reused as many times as needed in the script.
• When a map is applied, only the first encountered value from the mapped table is brought over.
• You can specify an optional value if no match is found for the mapped table.

Syntax for Mapping Load and ApplyMap

We first must create the map. Simply use a load prefix of “Mapping” to indicate this is a mapped table

• Note again that this is considered a temporary table in Qlik and will be dropped automatically at the end of the script.
• Also be aware that the first field is ALWAYS the key and the second field is ALWAYS the mapped value. The reason I emphasize this is because it is not important what the field names are called. Only the order matters.
• As long as the map table appears before we apply it, the map table can be anywhere in the script. I usually position it on the same script tab above the table I will be applying it to, but when I have maps that are applied several times, I might put that map load in script tab towards the front.

Then we can apply the map wherever needed. We will use the ApplyMap function where the first parameter is the map name (in single quotes), the second parameter is the key we use to translate the map from the current table and an optional third parameter will define a value when no match is found.

• A map can be applied multiple times within a script.
• We must use single quotes when referring to the map name.
• Note also that the key in the receiving table does not actually need to loaded as a separate field. In the example above, CustomerID has simply been commented out since we do not need to display this field in the app. We use it to translate the map but that is all we need it for.

Use Case 1 – Avoid Duplication in Fact Tables

Our central table is sacred. If we need to sum up sales or count rows or calculate any sort of metric, this is likely your fact table. It is important that maintain the integrity of this table and do not inadvertently add rows to it since that would mess up your calculations.

But there are times when we need to join data to this table. You might think that a left join is the way to do this. But what if the fields we use to join this table to our fact table actually have repeats? Notice in the charts below, when we do a left join, it will actually add records to the fact table. Now our totals are exaggerated for that customer.

If instead we do a mapping load, we ensure that only one of the ID records actually maps into the receiving table. In fact, the first record “Walmart” is mapped in and the second value “Wal-Mart” is discarded only because the load will encounter the “Walmart” value first in the current ordering of the map table.

Use Case 2 – Slowly Changing Dimensions – Only the Latest Value

This trait of mapping loads is also handy when we only want one value from the map but we want a specific value. Similar to the scenario above, if Walmart had changed their name at some point, we might want only the latest name. If we sort the table by the “Date Modified” field in descending order, we will only get the latest value. This can be helpful for mapping in slowly changing dimensions where we only want one of the values.

Use Case 3 – Easier to Manage than “Nested If’s”

Let’s consider a scenario where we have survey results on a numbered scale from 1-5. It might be handy to add the description for the numbers. We could create a “nested if” statement to handle this. This will work perfectly fine, but as this list gets larger, it might be difficult to maintain.

It might be better to map these values in, especially as the number of values in the list increases. Because the values can be provided in a table, this process becomes much easier to maintain as the number of distinct values grow.

Use Case 4 – Apply a Map and Do Math in the Same Step

Often times we must do a calculation within our script like “Quantity * Price as Extended_Amount”. But what about when the calculation we need must occur between fields that are not in the same table?

A mapping load can provide a quick answer here as well. The benefit here is that we can map the value in AND do the math in the same step. Without the mapping load function, we would be forced to join the second field to the first table and then reload the whole table using resident load.

In the example below, we need to come up with an extended cost of goods sold. But the standard cost of the item is retained in the item master, a separate table from our sales table. We will apply the cost to the sale record and do the calculation at the same time.

Use Case 5 – Reuse a Map Multiple Times

Often times, we will encounter an “attributes” table. This is a catch-all table that holds all textual descriptions for many ID fields. This single table might hold item descriptions, product families, reporting segments, etc. These tables have at least 3 columns: 1 for the value ID, 1 for the field ID and the text descriptor.

In this case, we can load this table as a map and then refer to it multiple times, wherever needed throughout our script, loading these text descriptions into appropriately named fields in our data model. Notice that we are concatenating the ValueID and the FieldID in the map and then using both fields in the map application as well. This ensures that if a ValueID has a repeat, we are not accidentally pulling text for a different field.

Below, we are applying the map once for our item description and then again for our category description.

Use Case 6 – Reference a Default Value When No Match Is Found

Often times, a business requirement will state that rather than a null value, we would like to see “No Description” for an item or “n/a” for example.

If you need to define a default value when there is no match, we can provide this value as a third parameter in the applymap function.

This example shows the simple use of a “no description” label when no match is found.

Use Case 7 – Reference a Backup Map When No Match Is Found

This is my favorite use-case for mapping loads. I have definitely had the situation where a client has said, “Well, first try find a match from this SQL table, but if there is no match, then try this spreadsheet, and if there is still no match, put n/a”.

Again, using the applymap third parameter, we can nest a backup applymap in the third parameter to meet this requirement. We could have an endless list of maps to satisfy the requirement, although I have never gone beyond three levels!

In this example, we will first go to the SQL table, then to a spreadsheet as a backup and then if there is still no match, we will add a manual description of “no description”.

Use Case 8 – Using a Map with Concatenated Keys

There might be times when our key needs to be a little more complex. Sometimes two or three fields are needed to key the mapped values to your receiving table, but as you recall, the mapping table can only have two fields.

In these cases, you can create one field that combines the keys. Just remember that you will have to create the same key when you apply the map. Learn Qlikview.

Source: Living Qlikview

When showing data in a month on month style the current month can often have a large drop off.  This can be prevented by providing a run rate for the current month.  This is where the known values for the current period are extrapolated forward for the rest of the period, providing an estimate of where the period will end.  For example, if you have a value of 1000 on day 10 of a 30 day month, you could calculate a run rate of 3000 for the month.

This solution was created in response to a question on Qlik Community, which you can find here:

Re: Last time value in time line chart

I have documented how this document works and the reasons why you might use it in a blog post here:

http://www.quickintelligence.co.uk/qlik-run-rate/

There is also a link to the QlikView version of this application on the blog post, and further discussions on why run rates are good to have.

I hope that you find it useful.  You will find other applications that I have uploaded under my profile on QlikCommunity.

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

Hi All,

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

Hope this helps !!!

please find the attachment for the eg: qvw and test fiels

Regards,

Avinashelite‌