Can you create a macro in QlickView?

[#1]

There is a database that only allows me to pull 5,000 records at a time via an API call.  Can I write a Macro in QlickView that will allow me to cycle through one load statement changing the "skip" "take" values in the API call?  Or do I just need to stack my load statements to pull all the records?

Situation:

Lets say I want to pull in 100,000 records from a database.  My only way to access the database is via API call.  But the API times out if you try to pull more than 5,000 records at a time.  So I set the API "skip & take" function to pull only 5,000 records.

Example API Load statement:

MyTable:

Load

    Field1, Field2, Field3, Field4

FROM '[https://myapi.source.net/?key=1234&param1=2&StartDate=2/1/2014 00:00:00&EndDate=2/28/2014 23:59:59&skip=0&take=5000] (XmlSimple, table is [MyTable/MyTable])';

What I want to do:

I want to iterate through the Load statement until I have pulled all 100,000 records.

Question:

Is it better to stack your load statements like this:

MyTable:

Load

    Field1, Field2, Field3, Field4

FROM '[https://myapi.source.net/?key=1234&param1=2&StartDate=2/1/2014 00:00:00&EndDate=2/28/2014 23:59:59&skip=0&take=5000] (XmlSimple, table is [MyTable/MyTable])';

Load

    Field1, Field2, Field3, Field4

FROM '[https://myapi.source.net/?key=1234&param1=2&StartDate=2/1/2014 00:00:00&EndDate=2/28/2014 23:59:59&skip=5000&take=5000] (XmlSimple, table is [MyTable/MyTable])';

Load

    Field1, Field2, Field3, Field4

FROM '[https://myapi.source.net/?key=1234&param1=2&StartDate=2/1/2014 00:00:00&EndDate=2/28/2014 23:59:59&skip=10000&take=5000] (XmlSimple, table is [MyTable/MyTable])';

Or

Can I write a Macro that will allow me to utilize the 1 load statement and just dynamically change the values of Skip & Take?

[giakoum · Accepted solution]

UPDATED : you do not need a macro you need a for next loop ...

for i = 0 to 19

Load

    Field1, Field2, Field3, Field4

FROM '[https://myapi.source.net/?key=1234&param1=2&StartDate=2/1/2014 00:00:00&EndDate=2/28/2014 23:59:59&skip=' & $(i)*5000 & '&take=5000] (XmlSimple, table is [MyTable/MyTable])';

next

[#3]

Check out the FOR loop functionality for loads. You can specify a list or you can even load all files in a directory

FOR EACH BILLFile IN FY13\NEG_BILL.PRN', 'FY14\NEG_BILL.PRN'

BILL:
LOAD

..

..

FROM $(BILLFile) (txt , codepage is 1252, no labels, delimiter is ',', msq);

[#4]

Hey David,

You can use a do loop function. This is commonly done while reading data from AD groups because of the number of records that could be pulled at a time limitation.

Original post:

Connecting to and Querying Active Directory for Users

you can tweak it per needs

Thanks

AJ

[#5]

Thanks Ioannis. An example of the final code that worked for me is below.  Its a combination of FOR -> NEXT & DO -> WHILE.  Your responses led me to a solution that works for my particular situation.

let vToday = Timestamp(Now(), 'MM/DD/YYYY hh:mm:ss' );

Let i=0;

for i = 0 to 19

do while 1 <= 19

Table1:

Load statement....

Sortdata:

Load * resident Table1 Order By Date;

Let vDate = Peek('Date',-1,'SortData');

drop Table Table1;

NoConcatenate

Incremental:

Load statement...

From [http://myapi.com/?key=123&StartDate=$(vDate)& 00:00:00&EndDate=$(vToday)&skip=&$(i)*5000$take=5000] (XmlSimple, Table is [Table1]);

Concatenate

Load statement....

Where not exists (DBRecordKey);  //prevents duplicate records from being loaded

Store Incremental into Table1.qvd(qvd);

Drop Table Incremental;

i=(i)+1;

Loop

Next

Table1:

Load statement....  // this loads the all the old and new data into the view.