topic Generate Rows in script to show 2 and 4 weeks from start date - which is different per person in QlikView

Generate Rows in script to show 2 and 4 weeks from start date - which is different per person

JoHandyside — Sat, 16 Nov 2024 02:01:30 GMT

I have rows of data as per below. I am trying to autogenerate rows in a table in the script so for each person i.e.;

Adam1 - I take the start date and generate a row for each date that is 14 days (frequency) from the start date until the end date, Likewise Beth 2 would generate a row for each date that is 28 days apart.

Original table looks like this

[Name, Date Start, Date End, Freq

Adam1, 01/01/2019,01/02/2019,14

Beth 2, 01/01/2019,01/02/2019,28]

***** end table something like this

[Name, Date Start, Date End, Freq, DueDate

Adam1, 01/01/2019,01/02/2019,14, 01/01/2019

Adam1, 01/01/2019,01/02/2019,14, 14/01/2019

Adam1, 01/01/2019,01/02/2019,14, 21/01/2019

Adam1, 01/01/2019,01/02/2019,14, 28/01/2019

Beth 2, 01/01/2019,01/02/2019,28,01/01/2019

Beth 2, 01/01/2019,01/02/2019,28,28/01/2019

]

Re: Generate Rows in script to show 2 and 4 weeks from start date - which is different per person

Frank_Hartmann — Wed, 06 Nov 2019 09:59:47 GMT

see attached qvw

Table: LOAD *, Date([Start date] + IterNo() - 1) as DueDate While [Start date] + IterNo() - 1 <= [End date]; Load * Inline [ Name, Start date, End date, Freq Adam1, 01/01/2019,01/02/2019,14 Beth2, 01/01/2019,01/02/2019,28 ]; NoConcatenate Table1: Load * Resident Table Where match(left(DueDate,2)/Freq,1,2,3,4,5,6,7,8,9,10,12,13,14);DROP Table Table; Concatenate Load *, [Start date] as DueDate; Load * Inline [ Name, Start date, End date, Freq Adam1, 01/01/2019,01/02/2019,14 Beth2, 01/01/2019,01/02/2019,28 ];

hope this helps

Re: Generate Rows in script to show 2 and 4 weeks from start date - which is different per person

JoHandyside — Wed, 06 Nov 2019 12:00:18 GMT

Thankyou for your response - this is not working completely as expected.

For adam1 it is showing,14th, 28th of the month for both months.

It needs to show the date dynamically ie 14 days after 28th Jan = 12th February, 26th Feb

Also other records may have a start date of the 2nd Jan 19 and frequency of 14 therefore the dates would show as 2/01/2019, 16,01/2019, 30/01/2019

Please can you adjust

Re: Generate Rows in script to show 2 and 4 weeks from start date - which is different per person

JoHandyside — Wed, 06 Nov 2019 13:11:28 GMT

Thankyou for your response - this is not working completely as expected.

For adam1 it is showing,14th, 28th of the month for both months.

It needs to show the date dynamically ie 14 days after 28th Jan = 12th February, 26th Feb

Also other records may have a start date of the 2nd Jan 19 and frequency of 14 therefore the dates would show as 2/01/2019, 16,01/2019, 30/01/2019

Please can you adjust

Re: Generate Rows in script to show 2 and 4 weeks from start date - which is different per person

Frank_Hartmann — Wed, 06 Nov 2019 13:47:05 GMT

see attached

hope this helps