Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Store a QlikSense table directly into an SQL table using the script.

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Store a QlikSense table directly into an SQL table using the script.

Last Update:

Oct 19, 2017 11:47:22 AM

Updated By:

OmarBenSalem

Created date:

Oct 19, 2017 11:47:22 AM

Hi community,

I assume we all, once in a while, faced this question : "Is it possible to store a table from QlikSense into our database?"


Since QlikSense is not made to write into a database but rather select from it, the idea is to create a procedure/function within the sql database, that will populate the table when called from the Qlik Sense script.

So, without further due, the answer is : YES !

I'll now share with you how I did to implement this:

1) The database should allow the creation of Procedures/functions.

For this example, I will be using postgresql.

a) create the table :

The first step is to create the table within the sql database :


example:

CREATE TABLE alerts

(

    alertname character varying(100) ,

    desk character varying(100)

)


b) create the Function:

The function writeback will be responsible of populating the table alerts:


CREATE OR REPLACE FUNCTION writeback(

alertname character varying,

desk character varying)

    BEGIN

      INSERT INTO alerts VALUES (alertname,desk);

    END;


c) Connect to the postgresql database and call the function:

Now, in the Qlik Sense script; what we want to do is store the table QlikSenseAlerts into the table alerts created within the Postgres db

QlikSenseAlerts:

//  1) the idea is to add rowNo() field to go through all the lines of our QlikSenseAlerts table

alerts:

load *,RowNo() as row inline [

Alert , Desk

alert1, desk1

alert2 ,desk1

alert3 , desk2

alert4, desk4

alert5, desk5

alert6, desk6

alert7, desk7

alert8, desk8

alert9, desk9

alert10, desk10

alert11, desk11

alert12, desk12

alert13, desk13

alert14, desk14

alert15, desk15

alert16, desk16

alert17, desk17

alert18, desk18

alert19,desk19

alert20,desk20

];

//2) connect to our database

LIB CONNECT TO 'PostgreSQL_localhost';


//3) line by line, store the value of each column in a variable

for a=0 to FieldValueCount('row')-1

Let vAlert = Peek('Alert',$(a), 'alerts');

Let vDesk = Peek('Desk',$(a),'alerts');


//4) call the function writeback that will store our 2 variables into the sql table; each variable contain the columns'value of the 1st row

SELECT public.writeback('$(vAlert)','$(vDesk)');


//5) do the same thing for all the lines

next a;


Result:

Table alerts in the sql table before reloading qlik sense script:

Capture.PNG

We, run the script:

Capture.PNG

Table alerts in the sql table after reloading qlik sense script:

Capture.PNG


Hope you'll like it

Omar BEN SALEM.

Comments
knightjc
Contributor
Contributor

I found this solution difficult to get working in an Oracle database as they don't naturally allow for insert statements from a function; however; by adding a PRAGMA directive it can be done in Oracle as well. 

This is a sample function that will work in Oracle:

CREATE OR REPLACE FUNCTION writeback (i_writeback_message IN VARCHAR2)

    RETURN INTEGER

IS

    PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  

    INSERT INTO xtmp_writeback_table (FIELD1) VALUES (i_writeback_message);

    COMMIT;

    RETURN SQLCODE;

END;

OmarBenSalem

The idea is to create a procedure in the database. The procedure/function will then differ depending on the database; each has its own syntax.

Thank you for sharing Jacob

peterkunhardt
Contributor III
Contributor III

Thank you omarbensalem‌, this is great! I've been mucking about with Foreign Data Wrappers without much success in order to create some staging tables in my data warehouse that source data from multiple databases, but this is a great solution to leverage Qlik to do both the multiple-database select as well as managing the schedule.

Question on your strategy here to make sure I understand, if I have several tables I would like to keep updated with this method, is it necessary to define a function on the db explicitly for each table, or is it possible to define an all-purpose function that would accept a table and column names as function arguments?

Thanks!

0 Likes
jonathandienst
Partner - Champion III
Partner - Champion III

You can execute stored procedures in a SQL statememt in Qlik. For example, with SQL Server, just use something like this:

SQL exec dbo.myProc('$(var1)', '$(var2)');

The proc does not need to return a rowset - it can simply be an insert or update statement.

I have used this technique to write audit logs to a database during reloads. I would not use it for a large scale data reload as it probably will not perform very well for many rows. For that you need probably need proper middleware like SSIS or Informatica.

pstiglich
Contributor II
Contributor II

The only problem with this approach is that it "RBAR" - row by agonizing row, i.e., very slow to loop through a large table and call the SP once per row.  Would be nice if we could pass in a Qlik table e.g., using SQL Server table valued parameter for an SP. 

Pete 

0 Likes
DropTable
Contributor
Contributor

hmm for Oracle you can write a procedure then call the procedure from the function.

 

0 Likes
Dante_83
Contributor III
Contributor III

Hello All,

This is great information. I've been trying find this capability for my team. 

0 Likes
Version history
Last update:
‎2017-10-19 11:47 AM
Updated by: