How To / Missing Manual

Hi All,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements. 

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this helps others.

Regards,

Jagan.

Me and a colleague have put together a new Calendar, combining a super fast method for generating the needed calendar, while also removing a lot configuration options in the previous methods.

So if you're using a  Min/Max based approach like the one described here: Creating A Master Calendar

Then you're properly used to waiting for the Min/Max to find the largest values in your dataset. This can take minutes. What we have here takes less than a second. I've actually not been able to time this, because this calendar script takes less than a 1 seconds even on billions of rows on decent hardware. The only exception to this is if you use a date range starting before christ and spans a thousand years. If you have a date range, with a lot of holes in it (dates with no data), then you should use the last parameter:

Call Calendar('[Date (NK)]','MyCal','Pre.','','true');

The Attached qvw shows both methods (remember the qvs file).

I've not commented it, because most people will not grasp what is going on anyway, and don't need to

To try it out, include the following in your script, and add this to your code:

Call Calendar('DateField');

/* ************************************************************************************

itelligence Standard Qlik Calender Torben Seebach/Martin Didriksen Special Thanks to Rob Wunderlich and John Witherspoon

Configure Calender parameters

  Syntax:

       Calendar(DateField[,CalendarName][,Prefix[,Suffix[,FullCalendar]]])

  Example:

       Call Calendar('Date (NK)','MyCal','Pre.','','true'); // creates a the MyCal table and fields are prefixed with Pre. In the superfast variat

       Call Calendar('Date (NK)'); //creates a table called calendar based of "Date (NK)" field

  Mandatory:

       @_DateField Contains the name of the field to connect the calendar to

  Optional:

       @_CalendarName Contains the name of the calendar we create      
       @_CalendarPrefix A Prefix for all fields
       @_CalendarSuffix A Suffix for all fields      
       @_FullCalendar If this contains a value then it creates a calendar with all dates from Min(Date) to Max(Date), default is the slowest option

************************************************************************************ */

Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)

  Let _StartTime = Now(); 
  Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
  Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
  Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
  Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
  Let _DateField = PurgeChar(_DateField,'"[]');

  "$(_CalendarName)":
   LOAD
        Distinct [$(_DateField)] as [$(_DateField)],
        Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
        Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
        Week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
        Week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
        Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
        Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)],         'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
        AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
        AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],

        AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial') as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;

  If _FullCalendar=1 Then
        LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
               While (_DateStart+(Iterno()-1)<=_DateStop);
        LOAD
               Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
               Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
        AUTOGENERATE FieldValueCount('$(_DateField)');
Else
        LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
        AUTOGENERATE FieldValueCount('$(_DateField)');
End If

  /* clean variables  */

Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;

ENDSUB

Updated 2014-05-23:

• Implement preceding load method suggested by Rob Wunderlich
• Fixed Timer to show seconds
• Corrected error in documentation
• Added PurgeChar so [Date A],"Date B" also works.
• Thanks to Stephen Redmond for pointing to a boolean error in line 22

Other than my brilliant Collegue Martin Didriksen, I also want to give John Witherspoon some credit for inspiration on the autogerate method used. I think he was the first who posted it online.

Over a year and half ago, I published instructions for creating a class to communicate with Qlikview api using the Python language. Here is the link for your convenience: QLIKVIEW AUTOMATION WITH PYTHON

Many of you have written and sent me messages requesting to make a new post containing the instructions to extract Qlikview pivot tables in excel and send them via email.

So after more than a year and a pandemic in action, here is an example of extracting Qlikview tables and sending as an email attachment. All tested with Python 3.9

You can found the full files and code at  GITHUB 

Here I post only the Python script.

Basically, there is a configuration file of the email parameters and the path of the qlikview file. The qlikview script defines the table  containing the people who must receive the emails with their addresses and the table fields to be filtered with the filter value to be used to reduce the data.

Hoping you like it,  for question or issues write me on GitHub.

Matteo Minischetti

PyQlikSender.py

from win32com.client import Dispatch  # pywin32 module
import smtplib
from email.message import EmailMessage
import os.path
from datetime import datetime
import configparser
import logging

current_path = os.getcwd()
config = configparser.ConfigParser()
config.read_file(open(r'PyQlikSenderConf.txt'))
QLIKVIEW_DOCUMENT = config.get('configuration','QLIKVIEW_DOCUMENT')
SMTP_ADDRESS = config.get('configuration','SMTP_ADDRESS')
SMTP_PORT = config.get('configuration','SMTP_PORT')
EMAIL_FROM = config.get('configuration', 'EMAIL_FROM')
EMAIL_LOGIN = config.get('configuration', 'EMAIL_LOGIN')
EMAIL_PASSWORD = config.get('configuration', 'EMAIL_PASSWORD')
LOGFILE = config.get('configuration', 'logfile')
QVRELOAD = config.get('configuration', 'QVRELOAD')


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

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

    def reload(self,doc):
        doc.Reload()

    def clearall(self,doc):
        doc.ClearAll()

    def apply_field_filter(self, doc, filter_field, filter_value):
        doc.Fields(filter_field).Select(filter_value)

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



def sendmail(email, subject,  name, surname, filename):
    msg = EmailMessage()
    msg['Subject'] = subject
    msg['From'] = EMAIL_FROM
    msg['To'] = email
    content = 'Dear ' + name + ' ' + surname + '  your report is attached here'
    msg.set_content(content)
    try:
        with open(filename, 'rb') as xls:
            msg.add_attachment(xls.read(), maintype='application', subtype='octet-stream', filename=xls.name)
    except Exception as e:
        print('cannot open attachment to send, error :' + str(e))
    try:
        with smtplib.SMTP_SSL(SMTP_ADDRESS, SMTP_PORT) as smtp:
            smtp.login(EMAIL_LOGIN, EMAIL_PASSWORD)
            smtp.send_message(msg)
    except Exception as e:
        logging.info('cannot send email to : ' + email + ' , error :' + str(e))


def manage_document(docname, current_path, tb_email):
    username = None
    password = None
    q = QlikView()
    version = q.app.QvVersion()
    print(version)
    doc = q.opendoc(docname, username, password)
    if QVRELOAD == 'Y':
        q.reload(docname)
    email_table = doc.GetSheetObject(tb_email)  # the object table containing email to send and filter to apply
    today_date = str(datetime.today().date()).replace('-', '_')
    rowiter = 0
    rows = email_table.GetRowCount()

    while rowiter < rows:
        title = email_table.GetCell(rowiter, 0).Text
        name = email_table.GetCell(rowiter, 1).Text
        surname = email_table.GetCell(rowiter, 2).Text
        email = email_table.GetCell(rowiter, 3).Text
        company = email_table.GetCell(rowiter, 4).Text
        filter_field = email_table.GetCell(rowiter, 5).Text
        filter_value = email_table.GetCell(rowiter, 6).Text
        tb1 = email_table.GetCell(rowiter, 7).Text
        print(title, name, surname, email, company)
        rowiter = rowiter + 1
        subject = "Qlikview report service for: " + company

        if rowiter > 1:
            filename = tb1 + '_' + name + '_' + surname + '_' + today_date + '.xls'
            q.clearall(docname)  # clear all filter
            doc.Fields(filter_field).Select(filter_value)  # apply filter
            try:
                chart = doc.GetSheetObject(tb1)
            except Exception as e:
                logging.info('error: ' + str(e))
            chart_path = current_path + '/' + filename
            chart.ExportBiff(chart_path)
            sendmail(email, subject, name, surname, filename)

    q.closedoc(doc)
    q.app.Quit()


if __name__ == '__main__':
    doc = current_path + '/' + QLIKVIEW_DOCUMENT
    logging.basicConfig(format='%(asctime)s - %(message)s', filename=LOGFILE, encoding='utf-8',
                        level=logging.DEBUG)
    tb_email = 'email'   # qlikview table containing email address and info
    manage_document(doc, current_path, tb_email)

Esta nota está publicada en el blog Qlikview en Español

Viene de una pregunta del foro publicada aqui

Si se tiene una tabla de ventas y se quiere predecir ventas futuras, teniendo algo como:

Para esto puede usarse la siguiente técnica que usa la ecuación de la recta:

PASO 1. Cargar datos de ejemplo:

SET NULLINTERPRET='';LOAD * INLINE [
    Fecha,     Venta
    1/2/2014,    11
    1/3/2014,     3
    1/4/2014,    10
    1/5/2014,     9
    1/6/2014,    11
    1/7/2014,     5
    1/8/2014,     8
    1/9/2014,    10
    1/10/2014,   11
    1/11/2014,   16
    1/12/2014,
    1/13/2014,
    1/14/2014,
    1/15/2014,];

PASO 2. Crear gráfico de linea con:

Dimensión:  Fecha

Expresión 1: (Poner "Pronostico" como etiqueta de expresión)

linest_m(total aggr(if(sum(Venta),sum(Venta)),Fecha),Fecha) // Pendiente

*Fecha // Mes a predecir

+linest_b(total aggr(if(sum(Venta),sum(Venta)),Fecha),Fecha) //Intercepto 

Expresion 2:

If(IsNull(Venta), Pronostico,sum(Venta))

PASO 3. En Propiedades de Grafico>Expresion desmarcar todas las opciones para la expresión 1, y marcarLinea para la expresion 2.

Queda así:

PASO 4. En el atributo Tipo de Linea de la Expresion 2, poner: if(IsNull(Venta),'<S2>')

Quedaría así:

El qvw de ejemplo puede descargarse aquí

Referencia:
http://community.qlik.com/thread/16846

Power Tools for QlikView

Version: 1.3.2

..again! A small update of one tool, and test of another.

Download here

https://www.dropbox.com/sh/8hac72aljgqlr7d/AAASkzbM2H1OoVcLE_wa0kWGa?dl=0

For the absolute latest version, please see Power Tools for QlikView - One-stop-shop

QlikView 12.X support matrix

Reviewed/updated with support for QlikView 12

• QlikView Server Agent *tested* - 1.6.2 - supported
• QlikView Server Super Agent *updated*- 1.7.3 - supported

Release notes here: Power Tools 1.3.2 for QlikView - Release Notes

Readme here: Power Tools 1.3.2 for QlikView - Readme

Want to collaborate?

Do you have questions, thoughts, suggestions or bugs to report on Power Tools? Please post in the forums on QlikCommunity: https://community.qlik.com/community/qlikview/management

Also check out our (currently dead, soon to be revived) Twitter: @QvPowerTools

DISCLAIMER

Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.

Attached is a QVD file with 249 countries tagged as officially accepted by ISO.

The country names and codes are compatible with for example Google's GeoChart extensions.

Fields included in the QVD are...

- Two letter country code, ISO 3166-1 Alpha2 code

- Three letter country code, ISO 3166-1 Alpha3 code

- Official country name

- Shorter version of country name, more suited for presentation

- Most common regions in the world

Primary ISO code source is ISO.org; ISO 3166-1 decoding table - ISO 3166 Maintenance agency - ISO

Get started with QlikView by first downloading and installing the QlikView Desktop product. This is video #1 in the multi-part Getting Started with QlikView video series designed to help you become more familiar with QlikView. Videos that will follow will cover connecting to and querying data, creating chart objects, defining expressions, and much more. Let's get started!

Note: Sample data used in the video series is attached to this post. I have included a Microsoft SQL Server backup file (.bak) or a multiple tab Excel file (.zip). If you wish to use this data, it will require you to restore or import it to your own Microsoft SQL Server.

You could also use QlikView to access the Excel files directly if you choose. We will cover the New Document Wizard a bit later in the video series.

For best viewing results, view the video in a higher resolution and full screen.

Note the location for the download is available by clicking the TRY or BUY button on the main page at qlik.com

Next Video - Connect and Query Data

This one-page datasheet provides an overview of the QlikView and Qlik Sense dual-use licensing offer, which gives all of your QlikView users the latest in Qlik technology for a modest uplift on your annual QlikView maintenance. 

Hello team.

One of the user asked that newly added columns in source should automatically populate in Qlikview work book without any changes to the work book.

I tried one POC for this case and implemented the same. Please find it in below attachment.

Note: This will not be feasible if we have more columns/rows in the table.

Hi,

I have seen many posts on community for showing negative values on charts like Block chart, pie chart etc. There is a solution available on community, but I thought to create the document so that everyone can utilize the same.

Lets say I have below data set

LOAD * INLINE [
Country, Score
US, 4971.765
UK, 4782.54
Japan, -200.941
India, 1412.181
Russia, 2445.292
];

Now I want to create the Block chart with above data set. So create the block chart with dimension as country and expression as sum(Score) , as soon as you create the chart you will get the error message that "positive and negative values in chart". But you can still display the negative values on chart with slight modification in expression.

Steps to create the block chart with negative value

1) Create the block chart

   Dimension : Country

   Expression : dual(sum(Score),fabs(sum(Score)))

If you want to display the distribution in % then use below expression

dual(num(sum(Score)/sum(TOTAL Score),'#0.00%'),fabs(sum(Score)))

Using dual function you can display the negative value with the base as positive value.

2) Go to chart properties -> Number Tab and check the Number format setting always as "Expression default". If the number format setting is not the expression default then negative values will be displayed as positive and also data will be displayed in wrong format. So if you want to provide any format like %, money you can define in the Dual expression itself using num function

Please see the attached application. In this application I have used the Fast change option so that you can see the results for multiple chart type as well

see also,

Control Chart

Benford's law

Bollinger Bands

Rolling N months

Regards,

Kushal

Hi Qlikers,

All of you might handling the large Fact tables load from QlikView on daily basis. When there is large Fact table, we always prefer to load the table either with Incremental load or we create day wise QVDs instead of loading the entire table again on daily basis and which is a good practice.

Here, I have a scenario where I have large fact table and I preferred to load the table by creating day wise qvds. I have created the script which will check if QVDs are created or not for particular day. If QVD is created it will not create it again (in for loop)  else it will always create the QVDs for Today, Yesterday and 2 Days ago ( with Today I want to create the yesterday and 2 days ago QVD also as there are some modifications could be done for Yesterday or 2 Days ago data ).

After creating all QVDs you can simply load all the QVDs in QlikView by *.qvd.

Just thought of sharing the script which will be helpful to people who are new to QlikView.

Below is the script highlighted

vDate_i is the variable which is used in where condition while we load the data from source database. Here, one thing should be noted that vDate_i variable format should be same as the Date Field in Source database table, so that the where condition will not fail while loading the data from source database table.

Please find the attached qvw file.

Hi All, 

Can anyone help me how can I get User documents for a certain user alone in qlikview API. I have already searched method regarding this in QMS Api latest version. But still cannot come up with the solution. Is there any possibility to get user documents alone by passing the user and domain name with server id?

Here is an easy approach to extracting the data model from a QlikView (QVW) file. The example documented uses QlikView Desktop, if you are using Qlik Sense then please click here.

Why would you use or need this kind of technique?

In a production environment you may find use of the output table script elements without the variables as they can be useful in load processes when transformed QVDs can be re-used by other applications. However, there are many times where you need to create something quickly for an ad-hoc piece of work or as a demonstration to show a customer with amended data to make it more relevant (changing product, department names etc). This approach can also be useful when working offline on the front end or application tuning where you do not want to deal with all of the complications of the ETL process.

Step 1 - Enter the file name and path for the QVW from which you wish to extract data

• Enter the full file path and name for the QVW
• e.g.  C:\Demo\Application\Executive Dashboard.qvw
  

Step 2 - Enter the destination folder path where the extract data will be saved

• Enter the full folder path for the destination folder
• e.g.  C:\Demo\Export Data

Step 3 - Select the output format type

• Your choices are either QVD (Qlik's proprietary file store format) or more generic CSV

Step 4 - Save and reload the application

Please note these instructions are also included in the dashboard itself in case you forget where it came from. This is what the dashboard looks like.

It's as easy as that.

The Most vital Set Analysis that we are using in creating our reports. Why we don't make it simple! This document will make it simple and straight forward to everyone. Good luck

Hi All,

Sometimes there may be requirements from users where they want to see the charts in from certain point of time like YTD, QTD, MTD, Last 5 Years etc., please find the set analysis expressions for this type of scenarios.

YTD Sales (Year To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today()))<=$(=Today())’}>} Sales )

Note: Year=, Quarter=, Month=, Week= excludes the selections in Year, Quarter, Month and Week dimensions.

QTD Sales (Quarter To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today()))<=$(=Today())’}>} Sales)

MTD Sales (Month To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today()))<=$(=Today())’}>} Sales)

WTD Sales (Week To Date)

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today()))<=$(=Today())’}>} Sales)

Last 5 Years Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Today(), -4))<=$(=Today())’}>} Sales )

Last 6 Quarters Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Today(), -5))<=$(=Today())’}>} Sales )

Last 12 Months Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )

Last 15 Weeks Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Today(), -14))<=$(=Today())’}>} Sales )

Last 10 Days Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Today()-9))<=$(=Today())’}>} Sales )

Yesterday Sales

Sum({<Year=, Quarter=, Month=, Week=, Date={‘$(=Date(Today()-1))’}>} Sales )

You can also arrive some flags for above scenarios in script and you use those flags in Set Analysis expression if your data always based on Current Date. Refer below link created by Richard.Pearce60‌

Calendar with flags making set analysis so very simple

Hope this helps.

Regards,

Jagan.

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.

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

Hi all,

the product JDBC Connector reached end-of-life because it's technically outdated. Support of it ends 2018-12-31.

The successor product is the TIQ Java Service Connector and can be obtained here:

https://www.tiq-solutions.de/en/tiq-java-service-connector/

Best regards,

Ralf

--------------------------------

Hi everyone,

this is a demo version of our JDBC Connector for QlikView. It will enable a lot of non-ODBC/OLEDB sources for loading data into QlikView.

Also, it would give much more performance on QV 9 loads (Oracle, mysql etc.) and could speed up on complex network environments (like VPN, firewalls, WAN).

Please try it out and give us some feedback!

Installation instructions:

• install the JDBC Connector and config the JDBC driver (mostly a .jar file)
• connect with JDBC URL (e.g. jdbc:oracle:thin:@localhost:1521:ex)
• example connect statement for Oracle (will be included by connect popup):
  CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:oracle:thin:@localhost:1521:ex;XUserId=SbKdMaFNRLOA;XPassword=aWELJaFNRbTA;";

Missing feature (waiting for QV support):

• no select option for database/owner on table wizzard

Thanks to my colleague Sven "Benno" Uhlig who brought up this idea and implementation!

Ralf

UPDATE:

This is a new version which supports DDL and DML statements too. You will get a result set if a DML statement was executed with the update count.

Just try script calls like this:

DATA:

SQL CREATE TABLE test2 (col1 INT)

;

SQL insert into test2 (col1) values (1)

;

SQL update test2 set col1=2

;

SQL drop table test2

;

UPDATE:

New version comes with an installer and config dialogs. See also: http://www.tiq-solutions.de/display/enghome/ENJDBC for a list of possible JDBC sources.