Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
