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.