topic Re: Create from value a valid iterable object in REST automation in Qlik Automate

Create from value a valid iterable object in REST automation

MartijnWanders — Tue, 28 Feb 2023 14:39:17 GMT

Hi People,

I need to extract my data with automation because it has to be send to Snowflake. Then the data will be transformed in Qlik Compose.

I'm stuck in a self created REST automation with nested data. I have multiple loops that retrieve the data. In the last loop I get a error message:

The last loop is based on a value field. For the most records it contains a value and in the example FILEM-1, see below:

But in attribute "attr_install_type" it will give again a nested value, see below:

I can imagen it can not iterate on this field but I can not find a solution. Is there something else which I can you to make the loop working?

It's hard to give the full json file of this automation because my customer is very keen on security etc..

I hope that someone had the same issue en solved it.

Thank you!

p.s: I tried to flatten the JSON but then the automation give other errors which I can not solve. But this is my plan B.

Martijn Wanders

Re: Create from value a valid iterable object in REST automation

blaise — Tue, 28 Feb 2023 16:09:58 GMT

I would go for the flatten solution. Another option is to load the data in a qs app and store it as csv and then use QAA to only upload the data to snowflake. You could also do an sql insert directly in qs script if you do not need any other features of QAA

Re: Create from value a valid iterable object in REST automation

MartijnWanders — Tue, 28 Feb 2023 18:21:18 GMT

Hi Blaise,

Thank you for your quick reply! I rather want to have to extract in a Qlik app.

Is it possible to make an insert statement in Snowflake table with Qlik script? Do you have an example? I never saw this and sounds very intresting.

Re: Create from value a valid iterable object in REST automation

blaise — Tue, 28 Feb 2023 20:08:21 GMT

there is a checkbox to mark when you set up the connection (something like allow non-select queries) and you have to add a specific keyword at the end of the statement but apart from that its a simple insert into <table> <list of columns> (values). A very simple example with only one column and one row to insert;

SQL INSERT INTO QLIK.TEST.TEST (C1) 
VALUES ('STRING ONE')
!EXECUTE_NON_SELECT_QUERY;

Re: Create from value a valid iterable object in REST automation

MartijnWanders — Tue, 28 Feb 2023 21:26:39 GMT

Hi Blaise,

Thanks for this information. I tried it and work perfectly. Now I insert it with one row at once. I'm going to investigate to create a bulk load.

Re: Create from value a valid iterable object in REST automation

blaise — Wed, 01 Mar 2023 07:12:53 GMT

I usually loop the insert statement with 100 rows for every iteration - works fine at takes way less time than doing it row by row. MSSQL though, snowflake might have other restrictions etc.

Re: Create from value a valid iterable object in REST automation

MartijnWanders — Wed, 01 Mar 2023 12:06:12 GMT

Hi Blaise,

Per 100 rows is working with Snowflake. But I have to create a lot of variables to manage this. I'm going to make a new thread for help with Flatten the file and Automation.

Re: Create from value a valid iterable object in REST automation

blaise — Wed, 01 Mar 2023 14:28:17 GMT

this is how i do it (not that many variables :))

LIB CONNECT TO 'Snowflake';

SQL TRUNCATE TABLE QLIK.TEST.TEST !EXECUTE_NON_SELECT_QUERY;

TmpUpload:
LOAD
num(class(rowNo()-1,100)) as TmpSqlRowClass,
rowNo() as TmpSqlRowNo,
'('
& if(len(Column1) < 1,'NULL',chr(39) & text(timestamp(Column1,'YYYY-MM-DD hh:mm:ss')) & chr(39) ) & chr(44)
& if(len(Column2) < 1,'NULL',chr(39) & text(Column2) & chr(39) ) & chr(44) 
& if(len(Column3) < 1,'NULL',chr(39) & text(Column3) & chr(39) ) & chr(44)
& if(len(Column6) < 1,'NULL', text(num(Column6,'#','.',' ')) ) & chr(44) 
& if(len(Column7) < 1,'NULL',chr(39) & text(Column7) & chr(39) )
& ')' as TmpSqlInsertString
FROM 
[lib://Data/SnowflakeUpload.qvd]
(qvd)
;

Upload:
LOAD
TmpSqlRowClass as SqlRowClass,
concat(distinct TmpSqlInsertString,',') as SqlInsertString
RESIDENT
TmpUpload
Group By
TmpSqlRowClass
;

Drop Table TmpUpload;

SET vSqlInsertString = "";

For i = 0 to NoOfRows('Upload') - 1
Let vSqlInsertString = peek('SqlInsertString',$(i),'Upload');

SQL INSERT INTO QLIK.TEST.TEST
(
Column1
,Column2
,Column3
,Column4
,Column5
,Column6
,Column7
)
VALUES
$(vSqlInsertString)
!EXECUTE_NON_SELECT_QUERY
;

Drop Table __skip_loading;

Next;

Re: Create from value a valid iterable object in REST automation

MartijnWanders — Thu, 02 Mar 2023 06:54:55 GMT

Hi Blaise,

Your script is so much better than what I had. I implemented and it works fine.

Thank you for sharing!