Dynamic Update and Dates

Tue, 13 Apr 2010 15:09:13 GMT

Hi There,

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

 oRS.close
 set sConn=nothing
 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!

Dynamic Update and Dates

Tue, 13 Apr 2010 15:24:58 GMT

I would convert different formatted dates into number. In VBScript, you can use CDbl. Or something like this CDbl(CDate(<date string >))

Dynamic Update and Dates

Tue, 13 Apr 2010 16:52:05 GMT

Unfortuantly Qlikview doesnt seem to support this command within the Macro, Unless im missing something?

Thanks for the reply.

Gez

Dynamic Update and Dates

Tue, 13 Apr 2010 19:25:12 GMT

QlikView Macro can be written on pure JavaScript or pure VBScript. You can use any VBScript functions in QlikView macro.

topic Dynamic Update and Dates in QlikView

Dynamic Update and Dates

Dynamic Update and Dates

Dynamic Update and Dates

Dynamic Update and Dates