Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
from excel files i am loading data to qlikview and after calculation/manupulation i need this data to be written back to database.
can we do this in qlikview?
Hi,
Check this url may do your job
http://community.qlik.com/message/135858
http://www.citagus.com/citagus/blog/qlikview-writing-back-to-database/
Regards,
Sokkorn
Hi,
It is possible to insert data into database through qlikview macro script.
Use following script to call a procedure in database
VB Script:
Set objADO = CreateObject("ADODB.Connection")
'Establish a connection
objADO.Open "connection_string"
'Set SQL
strSQL="call procedure"
'Begin Transaction
objADO.BeginTrans
'Execute Transaction
objADO.Execute strSQL, iAffected
objADO.CommitTrans
objADO.Close
Procedure:
Inside the procedure have some insert statements which in turn insert the data into database.
Hi,
Use the following macro script to get some values from sheet passed as parameter to database procedure
Sub Update_Comment
Dim objADO
Dim objRs
Dim strSQL
vcomment=ActiveDocument.GetVariable("a").GetContent.String
vfield = ActiveDocument.GetVariable("b").GetContent.String
'Create ADO Object
Set objADO = CreateObject("ADODB.Connection")
'Establish a connection
objADO.Open "connection_string"
'Set SQL
strSQL="call procedure('"&vcomment&"','"&vfield&"')"
'Begin Transaction
objADO.BeginTrans
'Execute Transaction
objADO.Execute strSQL, iAffected
objADO.CommitTrans
objADO.Close
end sub
HI, if You dont want to use VB, You can execute SQL in the LOAD Script
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security ...
TABLE:
LOAD * INLINE [
FIELD1, FIELD2
ROW1, NR1
ROW2, NR2
ROW3, NR3
];
ROWS_COUNT:
LOAD COUNT(FIELD1) AS ANZ RESIDENT TABLE;
LET V_ROWS = Fieldvalue('ANZ',1);
DROP TABLE ROWS_COUNT; //LET V_ROW = 1;
FOR V_ROW = 1 TO V_ROWS
LET V_FIELD1 = Fieldvalue('FIELD1',V_ROW);
LET V_FIELD2 = Fieldvalue('FIELD2',V_ROW);
SQL INSERT INTO TEST_TABLE1(FIELD1,FIELD2) VALUES('$(V_FIELD1)','$(V_FIELD2)');
NEXT;
Importent: If the table contains unique fields, Qlikview won't get any value for the 1+n fieldvalues.Therefore you has to write a exception with two variables V_FIELD1 V_FIELD_1_OLD
Hi,
Why FieldValue is holding repeated values for only one time ?
Any idea on this.
Thank in advance.
Hi,
use peek instead of FieldValue. You also don't need the ROWS_COUNT table:
OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security ...
TABLE:
LOAD * INLINE [
FIELD1, FIELD2
ROW1, NR1
ROW2, NR2
ROW3, NR3
];
For i= 0 to NoOfRows('TABLE') -1
LET V_FIELD1 = peek('FIELD1',$(i),'TABLE');
LET V_FIELD2 = peek('FIELD2',$(i),'TABLE');
SQL INSERT INTO TABLE(FIELD1,FIELD2) VALUES('$(V_FIELD1)','$(V_FIELD2)');
NEXT
Hi
I've just come across this post while trying to find a way to write data from QV to an SQL database and your script certainly seems to insert data into a table. However, when I insert a truncate line
SQL TRUNCATE Table SQL_Training.Table_Test_SQL_Load;
In the script it appears to be skipped when reloaded and instead I just keep inserting additional rows into the table. Any thoughts?
Cheers
Gareth
Hi
Have you tried OLEDB connexion instead of ODBC ?
hi
i do it but not work.
i think problem in line
objADO.Open "connection_string"
please send me a sample file