topic Re: Generate Rows according to Date Range in QlikView

Generate Rows according to Date Range

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have the following table (Date Format = DD/MM/YYYY):

Date	Start Date	End Date	Days	Rate	Client	Type	Amount
23/8/2018	23/8/2018	30/8/2018	7	10%	Client 1	DSC	30
23/8/2018	23/8/2018	30/8/2018	7	15%	Client 2	DSC	55
23/8/2018	23/8/2018	30/8/2018	7	18%	Client 3	DSC	85
27/8/2018					Client 5	BNK	15
27/8/2018					Client 8	BNK	20
27/8/2018					Client 10	BNK	25

What I need is, for those rows with Type = DSC, replicate a new row for each Client with values from Start Date to End Date:

Date	Start Date	End Date	Days	Rate	Client	Type	Amount
23/8/2018	23/8/2018	25/8/2018	3	10%	Client 1	DSC	30
23/8/2018	23/8/2018	30/8/2018	7	15%	Client 2	DSC	55
23/8/2018	23/8/2018	30/8/2018	7	18%	Client 3	DSC	85
27/8/2018					Client 5	BNK	15
27/8/2018					Client 8	BNK	20
27/8/2018					Client 10	BNK	25
24/8/2018	23/8/2018	30/8/2018	7	10%	Client 1	DSC	30
24/8/2018	23/8/2018	30/8/2018	7	15%	Client 2	DSC	55
24/8/2018	23/8/2018	30/8/2018	7	18%	Client 3	DSC	85
27/8/2018	23/8/2018	30/8/2018	7	15%	Client 2	DSC	55
27/8/2018	23/8/2018	30/8/2018	7	18%	Client 3	DSC	85
28/8/2018	23/8/2018	30/8/2018	7	15%	Client 2	DSC	55
28/8/2018	23/8/2018	30/8/2018	7	18%	Client 3	DSC	85

This would have to exclude Dates which are Saturdays and Sundays (by having only Work Days).

Is there any way to do this?

Thank you!!!

Re: Generate Rows according to Date Range

lucasanqlik — Fri, 28 Sep 2018 17:45:21 GMT

Hy, how are you ?

This is my first post in community

This is my solution your case:

BASE:

LOAD *,

Date(RangeSum([Start Date],IterNo()-1) , 'DD/MM/YYYY') AS [New Date],

IterNo() AS Line

While Days-IterNo() >= 0

;

LOAD Date,

[Start Date],

[End Date],

RangeSum(Days,1) as Days,

Rate,

Client,

Type,

Amount

FROM

(ooxml, embedded labels, table is Planilha1);

Bye.

Re: Generate Rows according to Date Range

rwunderlich — Fri, 28 Sep 2018 18:19:43 GMT

SET DateFormat='DD/MM/YYYY';

Data:

LOAD *

Where not match(WeekDay(Date), 'Sat', 'Sun')

;

LOAD

Date([Date] + IterNo()-1, 'DD/MM/YYYY') as Date,

[Start Date],

[End Date],

Days,

Rate,

Client,

Type,

Amount

FROM

[https://community.qlik.com/thread/315390]

(html, codepage is 1252, embedded labels, table is @1)

While ([Date] + IterNo() < [End Date]) and Type = 'DSC'

;

LOAD *

FROM

[https://community.qlik.com/thread/315390]

(html, codepage is 1252, embedded labels, table is @1)

Where Type = 'BNK'

;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

http://www.easyqlik.com