3 Replies Latest reply: Apr 13, 2010 3:25 PM by Nick Borunov RSS

    Dynamic Update and Dates

      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.

      error loading image

      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!