Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jacobesalas
Not applicable

Fill Dates between dates

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.

Tags (3)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Fill Dates between dates

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

];


Capture.PNG

3 Replies
sunny_talwar
Not applicable

Re: Fill Dates between dates

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

];


Capture.PNG

jacobesalas
Not applicable

Re: Fill Dates between dates

Thank you!

nicogene
Not applicable

Re: Fill Dates between dates

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:


Capture1.JPG

What is the best approach?

Thank you very much for your help.

Kind regards,

Nicolas.