Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Generate Rows according to Date Range

Hello,

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

DateStart DateEnd DateDaysRateClientTypeAmount
23/8/201823/8/201830/8/2018710%Client 1DSC          30
23/8/201823/8/201830/8/2018715%Client 2DSC          55
23/8/201823/8/201830/8/2018718%Client 3DSC          85
27/8/2018Client 5BNK          15
27/8/2018Client 8BNK          20
27/8/2018Client 10BNK          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:

DateStart DateEnd DateDaysRateClientTypeAmount
23/8/201823/8/201825/8/2018310%Client 1DSC          30
23/8/201823/8/201830/8/2018715%Client 2DSC          55
23/8/201823/8/201830/8/2018718%Client 3DSC          85
27/8/2018Client 5BNK          15
27/8/2018Client 8BNK          20
27/8/2018Client 10BNK          25
24/8/201823/8/201830/8/2018710%Client 1DSC          30
24/8/201823/8/201830/8/2018715%Client 2DSC          55
24/8/201823/8/201830/8/2018718%Client 3DSC          85
27/8/201823/8/201830/8/2018715%Client 2DSC          55
27/8/201823/8/201830/8/2018718%Client 3DSC          85
28/8/201823/8/201830/8/2018715%Client 2DSC          55
28/8/201823/8/201830/8/2018718%Client 3DSC          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!!!

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

2 Replies
lucasanqlik
Contributor
Contributor

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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