Archive for Qlikview
I wanted to share some QlikView settings that I use daily for communications with our iSeries box. I use the ODBC driver “iSeries Access ODBC Driver” (version 13.00.01.00) which are included on the iSeries Client Access installations (I have the v7r1 version installed).
I have the ODBC connection setup under System DSN (good idea if on a server that way all the connected users can use it) called “SYS400”. On the Server tab, I use a SQL naming convention which does take some getting used to if you are a heavy IBMer. When you use the SQL Naming convention, your sql connection strings in QlikView will end up in this type of format: (ODBC DSN Name).(Library).(Member).
Below is an example of a SQL read from the iSeries box which I’m using the data for translating a composite key of company and customer bill-to to a customer name.
An important note: IBM System i Access is a licensed program and does require a license to System i Windows Family (Product ID: 5761-XW1) before use. I would recommend checking with your department that handles the iSeries machine(s) for a client access install disc or possibly calling IBM to see about getting just the ODBC driver. For Client Access installation instructions, I would recommend this install guide from IBM.
The next part will cover additional ODBC settings and best practices for data retrieval on a iSeries machine. Stay tuned!
- add the extension with the QlikView Call (Qva.LoadExtension)
- add your special sauce! (I.E. What you need to do)
I always wanted to learn how to add extensions and with the help of a fellow redditor, we started tinkering with D3.js library in QlikView. Our attempts can be viewed on GitHub and are completely free for your use (Located here)
Once I learned the basics, I wanted to create some maps with QlikView. Currently, the only offering for mapping in QlikView was with third party product that needed to be purchased. I came up with the idea of using LeafletJS because of the simple API calls to append the map containers to the HTML, markers and other mapping layers. Additionally, Leaflet JS allows you to use your own choice of tile layers (i.e. Cloudmade, OpenStreetMaps, Google, etc…) and can allow the user to switch between them with a simple button click. I opted to use Google as most users are familiar the map style and the fact that the usage for them is pretty reasonable (read free with limits). Also, I wanted to be able to auto-cluster the data points so I included the Leaflet marker cluster repo as well (find it here).
With that, I want to promote my working Leaflet Qlikview Extension that I’m releasing under the GPL V2 license. Check it out on my QlikViewMaps repo and feel free to fork it! Also, I have included a screenshot of my extension that I’m using for my current employer. We are thinking of using the extension to map all of the current qualcomm events (30 min lag) with selectors to breakdown tractors, drivers and workflow event type.
I recently discovered this ingenious open source scripting components for Qlikview. It allows for easier scripting of Master calendars with much more capabilities.
I was posed with a problem at working for allowing our end users to easily create master calendars for the own Dashboards but they need to have the ability to use our Fiscal calendar instead of a standard calendar. I extended the Qlikview component scripts by creating my own calendar function that easily replicates our fiscal calendar business rules.
So now all the end users need to do is to include my dupre.qvs file and then use the call Dupre.FiscalCalendar.
Here is an example of call being used:
//Get the Min and Max of the Accident Date
min([Accident Date]) as minDate,
max([Accident Date]) as maxDate
//Assigning the Variables
lET vDateMin = num(Peek('minDate',0,'Temp'));
LET vDateMax = num(Peek('maxDate',0,'Temp'));
//Drop the Temp table as it is no longer used
DROP Table Temp;
//Indicate that we do want the set analysis variables created
SET Qvc.Calendar.v.CreateSetVariables = -1;
//Set the Field that we want qlikview to auto-join to
SET _Qvc.Calendar.v.Field.Date.Override = 'Accident Date';
//Create the FiscalCalendar with the Min and Max Date, name the table "FiscalCal" and prefix all the columns with "FS"
Here is the QVS:
An error has occurred. Please try again later.
For those wanting additional information on the qlikview components, check out their Google Project page: qlikview-components – A script library for Qlikview development – Google Project Hosting.