Store a QlikSense table directly into an SQL table using the script.
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 :
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)
INSERT INTO alerts VALUES (alertname,desk);
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
// 1) the idea is to add rowNo() field to go through all the lines of our QlikSenseAlerts table
load *,RowNo() as row inline [
Alert , Desk
alert3 , desk2
//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
//5) do the same thing for all the lines
Table alerts in the sql table before reloading qlik sense script:
We, run the script:
Table alerts in the sql table after reloading qlik sense script: