Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
MVP
MVP

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

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";

2 Replies
MVP
MVP

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

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

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

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

Community Browser