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: 
Not applicable

Create new rows in a table based on a start date and number of days column

I have the following table (I have only enclosed a subset of the table):

TerritoryDateTypeNumber of Days
SKAMC0610/02/2016Holiday3
RTSM32101/09/2015Meeting3
RTSM32107/09/2015Working5
RTSM32116/03/2015Holiday5
RTSM12518/03/2016Meeting4

I would like to transform it in the load process to the following using the date in the first table as the start date and the number of days as the number of rows to populate forward. I would also like to exclude weekends, so in the example 18/03/2016 is a Friday so when the rows are auto populated it does not include 19/03 and 20/03:

TerritoryDateTypeNumber of Days
SKAMC0610/02/2016Holiday3
SKAMC0611/02/2016Holiday3
SKAMC0612/02/2016Holiday3
RTSM32101/09/2015Meeting3
RTSM32102/09/2015Meeting3
RTSM32103/09/2015Meeting3
RTSM32107/09/2015Working5
RTSM32108/09/2015Working5
RTSM32109/09/2015Working5
RTSM32110/09/2015Working5
RTSM32111/09/2015Working5
RTSM32116/03/2015Holiday5
RTSM32117/03/2015Holiday5
RTSM32118/03/2015Holiday5
RTSM32119/03/2015Holiday5
RTSM32120/03/2015Holiday5
RTSM12518/03/2016Meeting4
RTSM12521/03/2016Meeting4
RTSM12522/03/2016Meeting4
RTSM12523/03/2016Meeting4

Any help would be gratefully received.

Thanks in advance

Chris

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like

Set DateFormat = 'DD/MM/YYYY';

LOAD *,

  LastWorkDate(Date, iterno()) as NewDate

INLINE [

Territory, Date, Type, Number of Days

SKAMC06, 10/02/2016, Holiday, 3

RTSM321, 01/09/2015, Meeting, 3

RTSM321, 07/09/2015, Working, 5

RTSM321, 16/03/2015, Holiday, 5

RTSM125, 18/03/2016, Meeting, 4

]

WHILE iterno() <= "Number of Days";

View solution in original post

2 Replies
swuehl
MVP
MVP

Maybe like

Set DateFormat = 'DD/MM/YYYY';

LOAD *,

  LastWorkDate(Date, iterno()) as NewDate

INLINE [

Territory, Date, Type, Number of Days

SKAMC06, 10/02/2016, Holiday, 3

RTSM321, 01/09/2015, Meeting, 3

RTSM321, 07/09/2015, Working, 5

RTSM321, 16/03/2015, Holiday, 5

RTSM125, 18/03/2016, Meeting, 4

]

WHILE iterno() <= "Number of Days";

Not applicable
Author

This has worked, many thanks for such a quick reply.