Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Store Qlik Sense table from Qlik Script into an Sql database

Hi everybody, sinanozdemir

What I want to do is creating a table in the script and then store it (all of its fields into a database)

I've got inspired by this:

Capture.PNG

Can you confirm that this would work?

alerts:

load Alert inline [

Alert

alert1

alert2

alert3

];

lib Connect To databaseName;

FOR Each a in FieldValueList('Alert')

dim:

let vAlert = a;

sql use databaseName

exec writeBack'$(vAlert)';

NEXT a


Thanks

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Store Qlik Sense table from Qlik Script into an Sql database

Thanks felip, but this is not it; this does the trick but will always have one row in excess:

this does it :

LIB CONNECT TO 'PostgreSQL_localhost';

//FOR Each a in FieldValueList('row')

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

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

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

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

   

next a;

6 Replies
felipedl
Valued Contributor III

Re: Store Qlik Sense table from Qlik Script into an Sql database

Hi Omar,

Should work all right as to Qlik Part of script (don't know how the data connection is configured and the stored procedure you're using).

Don't have a DB to test it, but seems to be ok.

Felipe.

OmarBenSalem
Esteemed Contributor

Re: Store Qlik Sense table from Qlik Script into an Sql database

See what I'm trying to do , stalwar1‌, your presence is always appreciated !

What I want to do, is going through all the lines (rowno()), and for each line store the Alert and Desk values into the database; but with the peek function I'm using, it always stores the first value..

alerts:

load *,RowNo() as row inline [

Alert , Desk

alert4, desk1

alert5 ,desk1

alert6 , desk2

];

LIB CONNECT TO 'PostgreSQL_localhost';

FOR Each a in FieldValueList('row')

Let vAlert = Peek('Alert',0, 'alerts');

Let vDesk = Peek('Desk',0,'alerts');

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

next a;


result in postgresql table:

Capture.PNG

what should I change?

felipedl
Valued Contributor III

Re: Store Qlik Sense table from Qlik Script into an Sql database

Your always picking the same row in the table with the peek statement when stating the zero row like Peek('Alert',0, 'alerts');.

You need a count of some sort to be able to get every row.

something like:



FOR Each a in FieldValueList('row')

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

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

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

next a;

OmarBenSalem
Esteemed Contributor

Re: Store Qlik Sense table from Qlik Script into an Sql database

almost there but, with peek, it always returning the privous value no?

see by yourself how It's stored in postgresql:

Capture.PNG

felipedl
Valued Contributor III

Re: Store Qlik Sense table from Qlik Script into an Sql database

I've updated the script Omar,

it should be lik bellow

FOR Each a in FieldValueList('row')

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

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

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

next a;



Since rowno() starts as 1, it needs to be $(a)-1 to get all values.


Felipe.

OmarBenSalem
Esteemed Contributor

Re: Store Qlik Sense table from Qlik Script into an Sql database

Thanks felip, but this is not it; this does the trick but will always have one row in excess:

this does it :

LIB CONNECT TO 'PostgreSQL_localhost';

//FOR Each a in FieldValueList('row')

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

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

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

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

   

next a;

Community Browser