topic Re: UPDATE SQL in QlikView

UPDATE SQL

Wed, 05 Feb 2014 18:00:27 GMT

Hola,

Tengo una tabla en Qlikview y me gustaría realizar un update en una tabla sql (con los mismos campos).

Codigo de ejemplo: Esta tabla qv necesito guardarla en una table sql (ya pude conectarme sin problemas)
OLEDB CONNECT TO .........

Directory;
PAGOS:
LOAD @1 as Empresa,
@2 as TotalFacturado,
@3 as Pendiente,

FROM
[..\PAGOS\*.xls]
(biff, no labels, table is HOJA1$)
WHERE(not IsNull(@1) And not IsNull(@2));

Muchas Gracias.

Saludos

Re: UPDATE SQL

Wed, 05 Feb 2014 18:35:29 GMT

Please translate your question into English.

Re: UPDATE SQL

Wed, 05 Feb 2014 18:53:45 GMT

I need to save the data in the following table in SQL.
I could now create the connection and see the sql table

Directory;
PAGOS:
LOAD @1 as Empresa,
@2 as TotalFacturado,
@3 as Pendiente,

FROM
[..\PAGOS\*.xls]
(biff, no labels, table is HOJA1$)
WHERE(not IsNull(@1) And not IsNull(@2));

//INSERT SQL......

Re: UPDATE SQL

Wed, 05 Feb 2014 20:54:29 GMT

Hi, If you want Insert the data from Excel file into Database you have different options other than Qlikview.

Qlikview is not cover all the ETL parts.

You can directly import the data from excel file into Database by using Db developer tools like TOAD, SQL Developer.

Or you can write shell script or batch file for this job.

Re: UPDATE SQL

agilos_mla — Wed, 05 Feb 2014 21:03:09 GMT

The only way you could use INSERT or UPDATE inn the QV script is by using a JDBC connection.

Re: UPDATE SQL

agilos_mla — Wed, 05 Feb 2014 21:03:22 GMT

A list of JDBC drivers and vendors can be found at: www.sqlsummit.com/JDBCVend.htm.

Re: UPDATE SQL

rwunderlich — Wed, 05 Feb 2014 21:41:34 GMT

You can use INSERT or UPDATE SQL through a standard ODBC/OLEDB Conection as long as you check "Open Databases in Read/Write Mode" on the Data Settings pane.

-Rob

Re: UPDATE SQL

Thu, 06 Feb 2014 13:52:56 GMT

Thank you very much to all.

I found the solution

I leave an example of the code

tip: Enable read / write database

// LOAD XLS
Directory;
LIQUIDACIONES:
LOAD @1 as Laboratorio,
     @2 as TotalFacturado,
     @3 as ALaboratorio

FROM
[..\Fuentes\*.xls]
(biff, no labels, table is hoja1$)
WHERE(not IsNull(@1) And not IsNull(@2) And not IsNull(@3 );

// INSERT SQL
For i= 0 to NoOfRows('LIQUIDACIONES') -1

          LET V_Laboratorio = peek('Laboratorio',$(i),'LIQUIDACIONES');

          LET V_TotalFacturado = peek('TotalFacturado',$(i),'LIQUIDACIONES');

         LET V_ALaboratorio = peek('ALaboratorio',$(i),'LIQUIDACIONES');


          SQL INSERT INTO Pagos(Laboratorio, TotalFacturado, ALaboratorio)
         VALUES('$(V_Laboratorio)','$(V_TotalFacturado)','$(V_ALaboratorio)');

NEXT

Re: UPDATE SQL

bumin — Mon, 24 Mar 2014 20:27:37 GMT

Hi Rob,

is "Open Databases in Read/Write Mode" a setting in the SQL-Server parameter?

thanks

Bumin

Re: UPDATE SQL

rwunderlich — Tue, 25 Mar 2014 04:17:16 GMT

It's a setting on the Data Settings panel in the script editor. It actually generates the RW mode into the CONNECT String.

-Rob