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:


    2) Qvw setting:

      QVW SETT.png

    3) Vbscript editor setting:




    sub UpdateFromTB4UP

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

        set conn = createobject("adodb.connection")
        Set objRecordSet = CreateObject("ADODB.Recordset") "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)

    Rem *** clear temp table


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

    end sub



    Shneior Dicastro