topic Re: Can you create a macro in QlickView? in QlikView

Can you create a macro in QlickView?

Thu, 13 Mar 2014 14:47:42 GMT

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])';

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

Re: Can you create a macro in QlickView?

giakoum — Thu, 13 Mar 2014 17:02:19 GMT

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])';

Re: Can you create a macro in QlickView?

Thu, 13 Mar 2014 18:11:41 GMT

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);

Re: Can you create a macro in QlickView?

Thu, 13 Mar 2014 18:23:36 GMT

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

Re: Can you create a macro in QlickView?

Tue, 18 Mar 2014 15:42:38 GMT

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

Table1:

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