topic IterNo +1and -1 in QlikView

IterNo +1and -1

surajap123 — Thu, 10 Apr 2014 13:06:20 GMT

Hi,

Could anyone explain me what each of the below statements does? especially the functionality of +1 and -1 in below statements.

1. While IterNo() <= ToDate - FromDate +1;

2. While IterNo() <= ToDate - FromDate -1;

3. Date($(vDateMin) + RowNo() -1) AS TempDate

Regards,

Suraj

Re: IterNo +1and -1

its_anandrjs — Thu, 10 Apr 2014 13:08:56 GMT

They are the conditional loops until they satisfy the condition they run the loop of statements. This is the function use with while clause and it returns integer values and literate with + 1 value.

In first Iterno() satisfy the condition until <= it runs the loop same in third

and in last on with rowno() the dates are created by increment one value with $(vDateMin)

Re: IterNo +1and -1

jolivares — Thu, 10 Apr 2014 13:38:20 GMT

IterNo() = Iteration Number (let's put as is)

So While and IterNo() create a loop inside the load statement in other programming languages you need to write a for... next loop.

Suppose you want to make a calendar table beginning 1/1/2014 = YourDate, to 15/1/2014. In this case you need to load your table as follows.

Load YourDate + IterNo() - 1

Autogenerate

While IterNo() <= 15/1/2014 - YourDate +1;

The first time IterNo() = 1, is the first iteration, so YourDate=1/1/2014 + 1 - 1, that means you begin this date, if you don't subtracts 1 your calendar begin 1/2/2014.

And in the while sentence, you need to control how many days your are generating: 15/1/2014 - 1/1/2014 = 14 days, but you need 15, so you need add 1 day more.

All above plus 1, minus 1 your can drop off, if you before begin said

Let YourDate = 1/1/2014 - 1 // in this case Your date is equal to 31/12/2013;

Load YourDate + IterNo()

Autogenerate

While IterNo() <= 15/1/2014 - YourDate;

I hope this explain...

Re: IterNo +1and -1

MK_QSL — Thu, 10 Apr 2014 13:46:25 GMT

Consider that

FromDate = Today and

ToDate = 15/04/2014

1) While IterNo() <= ToDate - FromDate +1;

The above Iteration will run until condition on right side of <= will get satisfied

i.e 6 times

QlikView stores date as Number format...

Try NUM(ToDate) in Text Box.. it will give you 41744

NUM(FromDate) = 41739

So ToDate - FromDate = 41744 - 41739 = 5

2) While IterNo() <= ToDate - FromDate -1;

I think you can now understand from above example

3) Date($(vDateMin) + RowNo() -1) AS TempDate

This is basically used in creating master calender TempDate between Min and Max Date range

Here vDateMin is a variable

FOR 1st ROW or First Iteration

$(vDateMin) + RowNo() - 1

=$(vDateMin) + 1 - 1

For 1st Row it will give you MinDate (i.e. $(vDateMin))

FOR 2nd ROW or 2nd Iteration

$(vDateMin) + RowNo() - 1

=$(vDateMin) + 2 - 1

=$(vDateMin) + 1

For 2nd Row it will give you MinDate + 1 (i.e. $(vDateMin))

and so on...

The Iteration will run until $(vDateMax)-$(vDateMin) + 1 times...

Hope this will clear to you....

Re: IterNo +1and -1

surajap123 — Thu, 10 Apr 2014 13:51:48 GMT

Hi Anand,

Thanks for the information.

In first Iterno() satisfy the condition until <= it runs the loop same in third

I pick up the first example, While IterNo() <=ToDate - FromDate +1;

I understand that the iterno() runs the loop, but what is <=Todate - FromDate+1 means?

Sorry, it it sounds basic..

Regards,

Suraj

Re: IterNo +1and -1

its_anandrjs — Thu, 10 Apr 2014 17:56:43 GMT

Ok I explain you about the Iterno( ) how it works with a simple example earlier also i explain that the Iterno( ) function returns integer 1 and it is work with while function until the condition is not satisfy and normally this function used in the master calendar for date creation. Where we want to create a date between any range like we say from any date to any end date. Here i explain i have two dates FromDate and Todate and this dates are used as Num dates because Iterno() works on the num dates.

Suppose the dates

FromDate = 01/04/2014

ToDate = 10/04/2014

1. While IterNo() <= ToDate - FromDate +1

and we convert this dates FromDate and ToDate into number by Num function

Num(FromDate) = 41730

Num(ToDate) = 41739

so if we see While statement and put values and at initial iterno() start from 1

While IterNo() <= ToDate - FromDate +1

While Iterno() <= 41739 - 41730 +1

then equation will be

While Iterno() <= 10

Means while loop runs 10 times until iterno() <= 10 while this loop runs our dates are created by adding 1 to value 41730 like 41731,41732......41740 and this are in num then we convert this dates into date by Date(Num(FromDate)).

Here we get date from 01/04/2014 to 10/04/2014

2.While IterNo() <= ToDate - FromDate -1

if we see equation here for while condition with the dates

Num(FromDate) = 41730

Num(ToDate) = 41739

Then,

While IterNo() <= ToDate - FromDate -1

While Iterno() <= 41739 - 41730 - 1

then equation is

While Iterno() <= 8

Means in this while condition loop runs 8 times and we are get 8 date values only

Here we get dates from 01/04/2014 to 08/04/2014

3.Date($(vDateMin) + RowNo() -1) AS TempDate

for this dates suppose Date($(vDateMin) is 01/04/2014

then we get the values for equation like

Date($(vDateMin) + RowNo() -1)

For first record

vDateMin + 1 - 1 = 41731

For Second record

vDateMin + 2 -1 = 41732

and so on until loop satisfy the condition then it will stop and a field is obtain named TempDate which contain date format and date from 01/04/2014 to 08/04/2014.

Regards

Re: IterNo +1and -1

surajap123 — Fri, 11 Apr 2014 14:23:15 GMT

Sorry for the delay in response. I was actually trying to understand each of the response.

The responses are really valuable to me. Thanks a lot for spending time to explain so well.

Every response is helpful to me. so, sorry if I didnt give full points.

Cheers !!