Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Vince_CH
Creator III
Creator III

Regarding InterNo()

Dear Community,

One quick question here regarding InterNo():

Can I use it for month as the unit instead of day, to create table of range?

if yes, in my script, there might to use InterNo() for two scenarios, for day, and for month, then how to differentiate the same InterNo()s?  thanks!

11.jpg

2 Replies
Gysbert_Wassenaar

IterNo() returns the number of the current iteration of a loop. There can be only one loop in a load statement using the While construct. So you can't have two different IterNo's. If you need both days and months than iterate over the days and use the Month() function to retrieve the number of the month.

If you want only months then you could iterate over the months:

LOAD
    *, Month(MonthStartDate) as Month;
LOAD
    IterNo() as Iteration
    MonthStart(StartDate,IterNo()-1) as MonthStartDate
WHILE
    MonthStart(StartDate,IterNo()-1)<=EndDate;
LOAD * INLINE [ ...etc

Note, the Day field that's created in the example you posted does not return the day of the month, but the number of the day in the period between the specified start and end date. The Day() function would return the day of the month

 


talk is cheap, supply exceeds demand
Vince_CH
Creator III
Creator III
Author

Dear Gysbert,
Thanks, I need some time to understand the conversion from days into month,
but If I want to get a range table like following, what the script should be like?
[Min(Month), Field similar like IterNo]
July.2018, 1
Aug.2018, 2
Sep.2018, 3
Oct. 2018, 4
Nov. 2018, 5
Dec.2018, 6
Jan. 2018, 7
.......

the background of above is that, i have potential orders forecasted in month, with actual realized dates differently . If realized before or in same month as the planed month, which are counted as closed, while if the realized month is later than planned month, the open quantity has to be counted in additoinal to original planned potential orders in same month. I am now trying to create referencemonth to count the open items, but need to understanding the working of IterNo firstly.