Load data day by day using loop

[qlikwiz123]

I have to query data from SQL based system. Due to the limitations and the available bandwidth, I cannot load the entire dataset in one SQL go.

How do I write my SQL query to pull 1 day worth of data and then loop through it until I have all the data till the maximum date?

I have a date field -RecordDate in the data table that I can use to identify the record date.

[mfchmielowski]

Hi,

You can prepare a calendar table and then use for each loop.

for each vDate in FieldValueList('yourDateField')
  Data:
  load *; sql statement with sqlDateCol = '$(vDate)';
next vDate;

[qlikwiz123]

@mfchmielowski Thank you. What exactly do you mean by calendar table?

[mfchmielowski]

@qlikwiz123 

Calendar table looks like this:

let vMinDate = num(date('2020-01-01', 'YYYY-MM-DD'));
let vMaxDate = num(date('2020-04-01', 'YYYY-MM-DD'));

Calendar:
Load
Date($(vMinDate) + IterNo() - 1) as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

You can declare dates range you want to fetch from sql.  And later on as i wrote before the code will look like:

for each vDate in FieldValueList('DateField')
  Data:
  load *; sql select * from SqlTable where SqlDateCol = '$(vDate)';
next vDate;

If you have the limitation of bandwidth consider storing results into qvd files and load them insted of sql queries.

[qlikwiz123]

@mfchmielowski 

Awesome. Will test this out and let you know how it goes. Thank you so much.

[Stewart021]

Then you can use it like this:

foreach (DateTime day in EachDay(StartDate, EndDate))

In this manner you could hit every other day, every third day, only weekdays, etc. For example, to return every third day starting with the "start" date, you could just call AddDays(3) in the loop instead of AddDays(1).

myprepaidcenter

[qlikwiz123]

@mfchmielowski 

let vMinDate = num(date('2020-01-01', 'YYYY-MM-DD'));
let vMaxDate = num(date('2020-04-01', 'YYYY-MM-DD'));

How can we make vMaxDate dynamic here so that it takes the maximum date available in the DateField? Also, my DateField is in YYYYMMDD format. If you could help me adjust the same in your expression, it would be really helpful

[mfchmielowski]

Hi.

Variables vMinDate and vMaxDate you can assing dynamicly. For example

Let vMinDate = num(today() - 7);

Let vMaxDate = num(today());

This is only preparation for calendar. You May also query database for max available date there and use peek function.

Dates:

Load min, max; sql select min(date) as min, max(date) as max From Table;

Let vMinDate=peek('Dates', 0,'min');

Let vMaxDate=peek('Dates', 0,'max');

Drop table Dates;

Create calendar as in previous post but add format.

Date($(vMinDate) + IterNo() - 1, 'YYYYMMDD' ) as DateField

And thats all. 

[qlikwiz123]

Hi @mfchmielowski 

Awesome. Thank you.

I see an issue here:

Calendar:
Load
Date($(vMinDate) + IterNo() - 1, 'YYYYMMDD') as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

In my case, vMinDate is 20210101 and vMaxDate is 20210421. The number of dates between these two is hardly 120 but DateField is giving 321 values, which is way more.

Also, For 'DateField' values, I see below numbers which are neither in YYYYMMDD or Floor. This is causing my loop to work incorrect.

[mfchmielowski · Accepted solution]

Hi @qlikwiz123 

Issue is that your dates is not a datetype field but a string type. Before using it to prepare calendar you need to prepare them to be a date. Dates (internaly) are dual or number. Thats why in previous examles i've used num(date(value, format')).

In that case the valid code will be:

let vMinDate = num(date#('20210101', 'YYYYMMDD'));
let vMaxDate = num(date#('20210401', 'YYYYMMDD'));

Calendar:
Load
Date($(vMinDate) + IterNo() - 1, 'YYYYMMDD') as DateField
AutoGenerate 1
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

 date#() creates datetype value from the input string by designed input format.

I've used yours input dates and that's the result. Hope it helps.