How to make Qlikview a real time system Update DB and Qlikview Online

shneior2 — Thu, 07 May 2015 07:12:48 GMT

Qlikview is E-TL offline (Transform, Load) tool like every standard BI tool, but with a lot flexible features that can make the using as you wish.

First, let’s talk about limitation.

QVW file include all the information: Data and design setting, actually It’s a “finished product”.

we can use Binary function, control and reorganized the data in the “Transform” and “Load” part.

When we finished all the process and arrived to the last part we can’t make any changes and probably not online

Up to version 11 the dynamic update feature get in

Dynamic update its feature that give as make query on virtual data model that floating on memory and show changes imminently

So what will upend after the reloading? all the changes will reset, the database not yet update.

To solve this issue that give us real time system we most to use integration of two component.

1) Vbscript for SQL query to the Data Base. (Data Base Update)

2) Dynamic Update query to the qvw model. (Qlikview Update)

Dynamic update setting

1) Server setting:

2) Qvw setting:

3) Vbscript editor setting:

4) VBSCRIPT MACRO LOOP UPDATE FROM TEMP TABLE:

sub UpdateFromTB4UP

Rem ***set 3 variable for every field in Temp Table****
    V1 = 0
    V2 = 0
    V3 = 0

Rem ****SET ODBC CONNECTION SETTING***
    set conn = createobject("adodb.connection")
    Set objRecordSet = CreateObject("ADODB.Recordset")
    conn.open "dsn=DB_QV;uid=;password=;"

Rem ****Get Table From SheetDocument****
    set TableBox = ActiveDocument.GetSheetObject("TB4UP")
    CellRect = ActiveDocument.GetApplication().GetEmptyRect()
    CellRect.Top = 0
    CellRect.Left = 0
    CellRect.Width = TableBox.GetColumnCount
    CellRect.Height = TableBox.GetRowCount
    set CellMatrix = TableBox.GetCells( CellRect )

Rem ****Loop for Insert 3 variable for every Line*****
    for RowIter=CellRect.Top+1 to CellRect.Height-1
      V1 = (CellMatrix(RowIter)(0)).Text
      V2 = (CellMatrix(RowIter)(1)).Text
      V3 = (CellMatrix(RowIter)(2)).Text

      SQL_String="UPDATE * SET FIELD1 = '1' WHERE FIELD2 = "&V1&";"

Rem **** Run Data Base server Query

objRecordSet.Open "UPDATE TABLE1 SET FIELD1 ='1' WHERE FIELD2 ="&V1&";",conn

Rem **** Run Dynamic Update

SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String)
next

Rem *** clear temp table

SET Result1 = ActiveDocument.DynamicUpdateCommand ("DELETE FROM UPDATE_DEBUG WHERE -1")

end sub

Shneior Dicastro

Re: How to make Qlikview a real time system Update DB and Qlikview Online

Anonymous — Thu, 07 May 2015 14:02:36 GMT

Hello,

How many rows can it UPDATE? I need to update about 200.000 rows.

Re: How to make Qlikview a real time system Update DB and Qlikview Online

b_garside — Wed, 13 May 2015 14:07:21 GMT

This looks very useful. Would it be possible to upload a Sample QVW to see it in action etc...?

Re: How to make Qlikview a real time system Update DB and Qlikview Online

jaimeaguilar — Mon, 11 Apr 2016 23:21:40 GMT

Hi,

take a look to KliqPlan. It is a solution based on the idea explained in this post. It uses a extension and is an enterprise-ready solution. It is not free but if you're really considering using an approach like this, you definitely should give it a try,

regards

article How to make Qlikview a real time system Update DB and Qlikview Online in Qlik Learning Documents

How to make Qlikview a real time system Update DB and Qlikview Online

Re: How to make Qlikview a real time system Update DB and Qlikview Online

Re: How to make Qlikview a real time system Update DB and Qlikview Online

Re: How to make Qlikview a real time system Update DB and Qlikview Online