Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

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

omarbensalem
Esteemed Contributor

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

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
New 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
Esteemed Contributor

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
New Contributor

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!

Version history
Revision #:
1 of 1
Last update:
‎10-19-2017 11:47 AM
Updated by: