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

     

    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:

    ServerSetting.png

    2) Qvw setting:

      QVW SETT.png

    3) Vbscript editor setting:

      Editorse.png

    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