Create from value a valid iterable object in REST automation

[MartijnWanders]

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

[blaise]

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 

[MartijnWanders]

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.

[blaise · Accepted solution]

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;

[MartijnWanders]

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. 

[blaise]

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.

[MartijnWanders]

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.

[blaise]

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;

[MartijnWanders]

Hi Blaise,

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

Thank you for sharing!