Friday, August 8, 2014

QV9 Dynamic Update

Again a great functionality in qlikview is Dynamic update. Through this we can write back to the dataset of qlikview. The written changes can be reflected in qlikview data model. Logically later we can write these changes can be written to qvd or xls or any other source file.
Below is my report UI


sub Insert
 set Result = ActiveDocument.DynamicUpdateCommand("INSERT INTO TEST  (SRNO,TYPE) VALUES (4,A)")
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub Update
 SQL_String="UPDATE TEST SET SR = 2 WHERE SRNO=3;"
  SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String)
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub Delete
 SQL_String="Delete from TEST where SRNO =4;"
  SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String)
  if Result = false then
    MsgBox Result.ErrorMessage
  end if 
end sub

sub SaveTableToFile
set obj = ActiveDocument.GetSheetObject("CH01")
obj.ExportEx "..\datafiles\6_dynamic_update.qvd", 4
end sub


INSERT INTO TEST (SRNO,TYPE) VALUES ($(vCnt), A $(vCnt))
UPDATE TEST SET CDATE = TODAY() WHERE SRNO=$(vCnt);
DELETE FROM TEST WHERE SRNO  = $(vCnt)

--script editor code copy and paste in below tab
// execute only once then comment it
//LOAD SRNO,
//    CDATE,
//    TYPE,SR;
//SQL SELECT *
//FROM SCOTT.TEST;
//
//
//store TEST into ..\datafiles\6_dynamic_update.qvd;

TEST:
LOAD SRNO,
     CDATE,
     TYPE,
     SR
FROM
[..\datafiles\6_dynamic_update.qvd]
(qvd);

set vCnt = 1;
 

No comments:

Post a Comment

web stats