topic Re: do while loop syntax error in App Development

do while loop syntax error

ioannagr — Tue, 20 Apr 2021 12:08:03 GMT

Hello all.

Table2 is very large so I'm asked to read every 100 lines of common ids and the relevant info and store these in a new table because i don't have the ram and storage space to load all tables at once and do incremental loads.

I have come up with this so far, it is not working. Any ideas?

Table1_ids:
LOAD pk as table1_pk;
SQL SELECT pk
FROM public.Table1
ORDER BY `pk` asc;

MaxTab1Pk:
LOAD max(pk) as MaxTab1Pk,
min(pk) as MinTab1Pk;
SQL SELECT pk
FROM public.Table1;

Let MaxTab1Pk = Peek('MaxTab1Pk', 0 , MaxTab1Pk);
Let MinTab1Pk = Peek('MinTab1Pk', 0 , MinTab1Pk);

Set a=$(MinTab1Pk);
//tab2_ids
Do while a<= $(MaxTab1Pk)

LOAD pk as table2_pk,
table1_fk as table1_pk;
SQL select `pk`, `table1_fk` as table1_pk
from public.Table2
where public.Table2.`table1_pk`= public.Table1.`pk`;

Let a=a+1;

//store to a load inline table? a csv?
Loop

Re: do while loop syntax error

Levi_Turner — Tue, 27 Apr 2021 23:14:55 GMT

If you can't do a full load into memory, then you'd want to store the table to disk in some format. From the Qlik side, QVDs are more optimized for loading back into Qlik, which means they are the ideal format. To mock up and example to PostgreSQL, this code is working on my end:

// Offload the min / max to the Database if possible; it'll save you a step MinMaxTable: LOAD maxid, minid; SQL SELECT max(id) AS maxid, min(id) AS minid FROM public.test; LET MaxID = PEEK('maxid', 0 , MinMaxTable); // Store Maximum value LET MinID = PEEK('minid', 0 , MinMaxTable); // Store Mininum value DROP TABLE [MinMaxTable]; // Drop the helper table since it isn't needed in future loads LET a=MinID; // Set A to the minimum. In my case, 1 LET b=a+99; // Set B to 100 more (inclusive) to A DO WHILE a <= $(MaxID) // Load Records between A and B [table]: LOAD id AS [id], t AS [text]; SQL SELECT "id", "t" FROM "public"."test" WHERE "id" >= $(a) AND "id" <= $(b); STORE [table] INTO [lib://data/loopExample/table_$(a).qvd] (QVD); // Store the table to a QVD DROP TABLE [table]; // Drop the table. Without the STORE / DROP, the table will auto-concat which puts us back to the starting point of the table not fitting into memory LET a=a+100; // Increment A to the next 100. In run 1, A will become 101 LET b=a+99; // Increment B to the next 100. In run 1, B will become 200 LOOP

Good luck

Re: do while loop syntax error

ioannagr — Wed, 28 Apr 2021 10:33:00 GMT

Hi @Levi_Turner , I figured it out already and did something very similar although yours looks very well put together, i will keep that in mind for future use 🙂 Very helpful!

Refering to that setting, please have a look at my next thread on this , maybe you could help me

https://community.qlik.com/t5/New-to-Qlik-Sense/store-qvd-script-routine-except-for-one-table/m-p/1803456#M184079

Thanks in advance!

This community rocks!

Re: do while loop syntax error

ioannagr — Thu, 29 Apr 2021 12:11:47 GMT

@Levi_Turner Hi, I run your script and it loads as you said but i saw it now, by 100 (1, 101,201 etc), while i wanted it to store batches of 100 ids each time, for example read id 1 to 100, then store, then go from 101 to 200 then store. How can I do this?