Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Oct 19, 2017 11:47:22 AM
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:
We, run the script:
Table alerts in the sql table after reloading qlik sense script:
Hope you'll like it
Omar BEN SALEM.
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;
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
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!
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.
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
hmm for Oracle you can write a procedure then call the procedure from the function.
Hello All,
This is great information. I've been trying find this capability for my team.
Omar, is this solution suitable for large datasets?