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;
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;
I think there is a need to provide some more information about Qlik and its other aspects as well.
ReplyDeleteQlik Rest Api Connection