topic Fill Dates between dates in QlikView

Fill Dates between dates

Anonymous — Thu, 05 May 2016 20:44:34 GMT

I have a date range, Start date and End date and total hours for that range per job.

What i have done is taken total hours/(End date-Start date) to create an average for each line. I would like to auto generate the dates between start and end dates.

For example this is the table I have.

JOB#, Start date, End date, Total hours, Total daily hours

12345, 5/10/16, 5/20/16, 100, 10

12346, 5/12/16, 5/22/16, 50, 5

What I would like to see is:

JOB#, Date, Total hours, Total Daily Hours

12345, 5/10/16, 100, 10

12345, 5/11/16, 100, 10

12345, 5/12/16, 100, 10

12345, 5/13/16, 100, 10

12345, 5/14/16, 100, 10

12345, 5/15/16, 100, 10

....

12345, 5/20/16, 100, 10

12346, 5/12/16, 50, 5

12346, 5/13/16, 50, 5

....

12346, 5/22/16, 50, 5

any help is greatly appreciated. Thank you.

Re: Fill Dates between dates

sunny_talwar — Thu, 05 May 2016 20:53:11 GMT

Try this:

Table:

LOAD JOB#,

Date([Start date] + IterNo() - 1) as Date,

[Total hours],

[Total daily hours]

While [Start date] + IterNo() - 1 <= [End date];

LOAD * Inline [

JOB#, Start date, End date, Total hours, Total daily hours

12345, 5/10/16, 5/20/16, 100, 10

12346, 5/12/16, 5/22/16, 50, 5

];

Re: Fill Dates between dates

Anonymous — Thu, 05 May 2016 21:33:14 GMT

Thank you!

Re: Fill Dates between dates

nicogene — Sat, 01 Sep 2018 11:22:04 GMT

Hello Sunny,

I hope this message finds you well.

I would like to recreate each row per day (screenshot below) and excel file in attachment:

What is the best approach?

Thank you very much for your help.

Kind regards,

Nicolas.