topic For loops, start/end dates and keytables in QlikView

For loops, start/end dates and keytables

Anonymous — Wed, 08 Jun 2016 13:16:10 GMT

Hi everyone,

I have the foloowing table (simplified of course):

Code	FROM_date	TO_date	Manager
100	01 - 01 - 2016	03 - 01 - 2016	Hank
101	01 - 01 - 2016	05 - 01 - 2016	Edward

Now what i want to make is the following table

DAY-CODE-KEY	Manager
01-01-2016-100	Hank
02-01-2016-100	Hank
03-01-2016-100	Hank
01-01-2016-101	Edward
02-01-2016-101	Edward
03-01-2016-101	Edward
04-01-2016-101	Edward
05-01-2016-101	Edward

So basically i want to load each row in the top-table and then use a FOR-loop to create rows in a new table.

I was thinking to loop through the lines, then set two vars with peek (from and to), and then loop from 'from' to 'to' and create the lines in table 2.

Am I thinking in the wrong direction? Cause I can't seem to nest two for statements....

Kind regards,

Herb

Re: For loops, start/end dates and keytables

Anonymous — Wed, 08 Jun 2016 16:10:20 GMT

Herb,

I think you can use the intervalmatch function. Take a look at this script and see if it can help you along the way.

best regards,

Helena

Re: For loops, start/end dates and keytables

sunny_talwar — Wed, 08 Jun 2016 16:19:42 GMT

Another options:

Intervals:

LOAD Date,

Code,

Date & '-' & Code as [DAY-CODE-KEY],

Manager;

LOAD Date(FROM_Date + IterNo() - 1) as Date,

Code,

Manager

While FROM_Date + IterNo() - 1 <= TO_date;

Load * Inline [

FROM_Date, TO_date, Code, Manager

01-01-2016, 03-01-2016, 100, Hank

01-01-2016, 05-01-2016, 101, Edward

];

Re: For loops, start/end dates and keytables

Anonymous — Thu, 09 Jun 2016 11:03:22 GMT

Thanks, this time I chose the iterative solution provided by Sunny.

Thanks for the example of proper use of an interval function.

Re: For loops, start/end dates and keytables

Anonymous — Thu, 09 Jun 2016 11:03:41 GMT

Thanks, works exactly as needed.