Generate Rows according to Date Range

[microwin88x]

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!!!

[lucasanqlik]

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.

[rwunderlich · Accepted solution]

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