topic AW:loop within LOAD in QlikView

loop within LOAD

tresB — Mon, 12 Jul 2010 05:45:33 GMT

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

AW:loop within LOAD

brenner_martina — Mon, 12 Jul 2010 08:19:59 GMT

Hi,

you can use a if-then-else statement:

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

and than use Sum(CountTuesdays)

AW:loop within LOAD

tresB — Mon, 12 Jul 2010 09:03:18 GMT

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.

AW:loop within LOAD

johnw — Tue, 13 Jul 2010 02:06:37 GMT

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.

AW:loop within LOAD

tresB — Tue, 13 Jul 2010 11:29:11 GMT

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)?

AW:loop within LOAD

johnw — Tue, 13 Jul 2010 17:45:57 GMT

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.

AW:loop within LOAD

rwunderlich — Wed, 14 Jul 2010 05:05:32 GMT


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

AW:loop within LOAD

tresB — Fri, 16 Jul 2010 07:03:08 GMT

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

Thanks ance again.