topic Re: Using FOR to double records within a range of weeks in QlikView

Using FOR to double records within a range of weeks

rudywelvaert — Tue, 01 Mar 2016 11:01:57 GMT

Hi,

I need to split up records (make doubles but with different week indication) into several records by week within a range of weeks.

First I make records from first start week and then add the records for the following weeks if needed.

Example:

Starting set of records:

Records that have to be split up:

End result:

I am using For statement with dates.

But I don't get the expressions correct.

FOR vWeek = $(vWeekFromRow) to $(vWeekTillRow) step 7

I get errors like

Please check the attached Tasks.qvw file for details

Re: Using FOR to double records within a range of weeks

petter — Tue, 01 Mar 2016 11:19:29 GMT

Line 64 and 65 in your Load Script assigns dates to the two variables. In line 67 when you do a $-sign expansion - that is not necessary. It will actually not recognize this as dates but as a division and thus make a small number which is a real - which again isn't good for a FOR NEXT loop...

Running your Load Script with the Debugger will reveal what I just said:

STEPPING FURTHER:

Lastly I think in general that nested FOR NEXT loops here might be very inefficient compared to doing a WHILE in your loop and possibly using preceding loads too.

Re: Using FOR to double records within a range of weeks

rudywelvaert — Tue, 01 Mar 2016 13:56:33 GMT

Thanks Petter,

I tried to change the code using a While in stead of a For with two extra variables vWeek and vWeekTill

Also needed to change the Where statement: Where TEMP_TASKS_2_ID = $(vRowNo);

Now I get this result:

in stead of

It looks as if the loop variables vWeek and vWeekTill are not evaluated on the right moment.

Re: Using FOR to double records within a range of weeks

petter — Tue, 01 Mar 2016 17:28:48 GMT

Sorry ... I wasn't clear enough. I should have said the WHILE clause that is part of the LOAD statement. It is much more efficient in iterating through rows/records - but it pose more challenging. I think I will have a closer look at it in a couple of hours to give you more detailed suggestions and give you some pointers on how to use it...

Re: Using FOR to double records within a range of weeks

petter — Tue, 01 Mar 2016 18:36:16 GMT

This is how you could use the LOAD .... WHILE logic - which is more straightforward:

The WHILE makes the LOAD iterate over each row while the condition in the WHILE part is satisfied. You will often use the IterNo() function that gives you the number of the iteration it is currently in for the given row to get the right number of iterations.

In this LOAD the number of iterations that has to be taken for each row will be satisfied by either:

STATUS = 'OPEN' AND IterNo() <=" TheNumberOfWeeksBetween_WEEK_TILL_AND_WEEK_FROM"

"TheNumberOfWeeksBetween_WEEK_TILL_AND_WEEK_FROM" calculated like this: Floor((WEEK_TILL-WEEK_FROM)/7)+1

STATUS = 'CLOSED' AND IterNo() = 1 ( We should include every CLOSED status but not iterate further)

Re: Using FOR to double records within a range of weeks

rudywelvaert — Tue, 08 Mar 2016 09:51:40 GMT

Thanks Petter,

this helped me a lot.

Re: Using FOR to double records within a range of weeks

petter — Tue, 08 Mar 2016 12:05:49 GMT

If you considered this as final please mark the question as answered

Re: Using FOR to double records within a range of weeks

rudywelvaert — Tue, 08 Mar 2016 12:17:27 GMT

Petter,

I would like to, but when I click actions I get this:

I only can indicate if it were Helpfull

And I can Like it

Re: Using FOR to double records within a range of weeks

Anonymous — Tue, 08 Mar 2016 18:58:32 GMT

Hi Rudy,

Thanks for your post. You can mark a reply as Correct by clicking the "Correct Answer" button at the bottom of the reply (with the green star). For more details, see: Qlik Community Tip: Marking Replies as Correct or Helpful

Best regards,

Qlik Community Moderation Team