topic Re: Can we insert data or records from qlikview to Database in QlikView

Can we insert data or records from qlikview to Database

Anonymous — Fri, 14 Sep 2012 03:08:37 GMT

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?

Re: Can we insert data or records from qlikview to Database

Sokkorn — Fri, 14 Sep 2012 05:00:11 GMT

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

Re: Can we insert data or records from qlikview to Database

Fri, 14 Sep 2012 05:09:12 GMT

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.

Re: Can we insert data or records from qlikview to Database

Fri, 14 Sep 2012 05:22:49 GMT

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

Re: Can we insert data or records from qlikview to Database

Tue, 25 Sep 2012 14:34:22 GMT

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

Re: Can we insert data or records from qlikview to Database

Wed, 03 Apr 2013 13:03:26 GMT

Hi,

Why FieldValue is holding repeated values for only one time ?

Any idea on this.

Thank in advance.

Re: Can we insert data or records from qlikview to Database

Wed, 10 Jul 2013 06:09:41 GMT

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)');

Re: Can we insert data or records from qlikview to Database

Fri, 08 Jan 2016 15:38:32 GMT

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

Re: Can we insert data or records from qlikview to Database

pascal_theurot — Thu, 24 Mar 2016 09:43:47 GMT

Have you tried OLEDB connexion instead of ODBC ?

Re: Can we insert data or records from qlikview to Database

mjr6662000 — Mon, 16 May 2016 08:58:35 GMT

i do it but not work.

i think problem in line

objADO.Open "connection_string"

please send me a sample file

Re: Can we insert data or records from qlikview to Database

krishna789 — Mon, 25 Sep 2017 12:09:21 GMT

java script can you show