Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
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.

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

Thank you!

nicogene
Partner - Contributor III
Partner - Contributor III

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.