topic Re: Load data day by day using loop in QlikView

Load data day by day using loop

qlikwiz123 — Thu, 15 Apr 2021 14:04:44 GMT

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.

Re: Load data day by day using loop

mfchmielowski — Fri, 16 Apr 2021 18:56:00 GMT

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;

Re: Load data day by day using loop

qlikwiz123 — Fri, 16 Apr 2021 20:39:12 GMT

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

Re: Load data day by day using loop

mfchmielowski — Sat, 17 Apr 2021 07:57:35 GMT

@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.

Re: Load data day by day using loop

qlikwiz123 — Mon, 19 Apr 2021 16:43:31 GMT

@mfchmielowski

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

Re: Load data day by day using loop

Stewart021 — Wed, 21 Apr 2021 07:00:51 GMT

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

Re: Load data day by day using loop

qlikwiz123 — Thu, 22 Apr 2021 21:13:44 GMT

@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

Re: Load data day by day using loop

mfchmielowski — Fri, 23 Apr 2021 13:57:36 GMT

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.

Re: Load data day by day using loop

qlikwiz123 — Fri, 23 Apr 2021 15:51:21 GMT

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.

Re: Load data day by day using loop

mfchmielowski — Fri, 23 Apr 2021 16:22:00 GMT

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.

Re: Load data day by day using loop

qlikwiz123 — Mon, 26 Apr 2021 19:17:57 GMT

@mfchmielowski

Wonderful. Thank you