This content has been marked as final. Show 3 replies
Hope someone can help. Each night a new set of QVD files are generated. Some applicaltions we use require live data so we have been looking at using the Dynamic update function.
When retrieving the field DateUpdated from the SQL Server and adding it to the DateUpdated Table within Qlikview using DynamicUpdate the dates seem to appear in a different format. Using a list box it would appear that one is 12hr and one is 24hr and therefore any calculations we do on DateUpdated appears to exclude data for the current date.
The Macro i use to retive the DateUpdate is as follows
Set oRS =CreateObject("ADODB.Recordset")
sSQL="Select ID,DateUpdated,UserID,CAST(Closed AS INT) * - 1 AS Closed,CompanyID,EmployeeID,Priority from [WORK] WHERE DateUpdated>dateadd(minute,-20,'" & WorkDate & "') OR DateCreated>dateadd(minute,-20,'" & WorkDate & "')"
oRS.Open sSQL, oconn,3,2
Values = oRS.GetRows
' Opens table
' Pulls all the rows back into an array called Values
' Closes connection
For r = LBound(Values, 2) To UBound(Values, 2)
SET Inputting = ActiveDocument.DynamicUpdateCommand ("DELETE FROM QVWork WHERE WorkID=" & Values(0, r))
QlikSQL = "INSERT INTO QVWork (WorkID,DateUpdated,UserID,Closed,CompanyID,EmployeeID,Priority) VALUES (" & Values(0, r) & ",'" & Values(1, r) & "'," & Values(2, r) & "," & Values(3, r) & "," & Values(4, r) & "," & Values(5, r) & "," & Values(6, r) & ")"
SET Inputting = ActiveDocument.DynamicUpdateCommand (QlikSQL)
How can I make sure that the date retrieved in the Macro is formatted the same as the date in the QVD file, I'm assuming that it's the difference between Qlikview SQL connector and the Macro Interpreter?
Any Pointers will be greatly Received!