loop within LOAD

[tresesco]

Hi All,

I have two date fields (startdate and enddate). I want to calculate the number of Tuesdays between these dates (for every record)

within the script during load itself. I think the loop is needed to be used within the LOAD. Please help me out.....

[brenner_martina]

Hi,

you can use a if-then-else statement:

If(Weekday(datefielt)=2,1,0) as CountTuesdays

and than use Sum(CountTuesdays)

[tresesco]

Thank you Martina, for your attention.

But what i want a little different. Let me explain once again.

two fields i have -- START_DATE and END_DATE. now i need to check(and count) if there is any Sunday in between these two dates. Logically I would require to run a loop for every record .

STAR_DATE END_DATE NO-OF SUNDAYS _between

10/10/2010 10/12/2010 ??

05/05/2010 06/07/2010 ??

Please show me a way out.

[johnw · Accepted solution]

I'm heading home so won't try to build the actual expression. But you can count weeks by just (END_DATE - START_DATE)/7. Then it's just a matter of adjusting the first and last week if necessary, based on what those days of the week are.

[tresesco]

Thank you John for your idea !

Can you please let me know, if there is any way of implementing loop within the LOAD (inter-record loop)?

[johnw]

Well... probably, but it would be much less efficient, and I'd have to study the exact syntax since I use loops so rarely. But maybe something along these lines:

set count = number of rows in the table;
for i = 1 to count
set Tuesdays = 0
for date = (first Tuesday on or after START DATE) to (last Tuesday on or before END_DATE)
add 1 to Tuesdays
next
LEFT JOIN ([My Table])
LOAD fieldvalue('KeyField',$(i)), $(Tuesdays) as NumberOfTuesdays
AUTOGENERATE 1;
next

But yikes is that ugly. There's probably a more efficient loop, but a calculation is much faster.

[rwunderlich]

tresesco wrote:Can you please let me know, if there is any way of implementing loop within the LOAD (inter-record loop)?

John's calculation suggestion is probably the best way to address your original question. To answer your question re looping. The typical Qlikview method of looping within records is using WHILE and Iterno(). Here's a script that counts Sundays using that technique.

data:
LOAD * INLINE [
STAR_DATE, END_DATE
10/10/2010, 10/12/2010
05/05/2010, 06/07/2010
07/04/2010, 07/06/2010
07/05/2010, 07/06/2010
07/04/2010, 07/11/2010
07/04/2010, 07/12/2010
]
;

LEFT JOIN (data)
LOAD
STAR_DATE,
END_DATE,
sum(SundayCount) as SundayCount
GROUP By STAR_DATE, END_DATE
;
LOAD
STAR_DATE,
END_DATE,
if(Weekday(STAR_DATE + iterno()-1) = 'Sun', 1, 0) as SundayCount,
date(STAR_DATE + iterno()-1) as Date
RESIDENT data
WHILE STAR_DATE + iterno()-1 <= END_DATE
;

-Rob

[tresesco]

Thanks a lot Rob and John ! Your codes are really helpful. I admire your logics.

Thanks ance again.