How to get all the dates in between start date and end date for each transaction?

[mwallman]

Hi all,

I have two date fields. Example:

OrderID	Value	StartDate	EndDate
1	100	01/01/2016	13/01/2016
2	22.99	24/02/2016	28/02/2016
3	34.99	14/03/2016	19/03/2016
4	59.99	17/01/2016	29/01/2016
5	87.99	10/03/2016	13/03/2016

How can I generate a table that has all the possible dates between the start date and end date for each order?

Is there a script solution for this please?

[sunny_talwar]

May be this:

Table:

LOAD OrderID,

  Value,

  StartDate,

  EndDate,

  Date(StartDate + IterNo() - 1) as Date

While Date(StartDate + IterNo() - 1) <= EndDate;

LOAD OrderID,

    Value,

    StartDate,

    EndDate

FROM

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

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

[swuehl]

It may be needed to create a link table between Date / Mastercalendar and your Order table, to avoid duplication of facts:

Table:
LOAD OrderID,
    Value,
    StartDate,
    EndDate
FROM
[https://community.qlik.com/thread/211240]
(html, codepage is 1252, embedded labels, table is @1);


DateOrderLink:
LOAD OrderID,
  Date(StartDate + IterNo() - 1) as Date
RESIDENT Table
While Date(StartDate + IterNo() - 1) <= EndDate;

See also

Creating Reference Dates for Intervals

[mwallman]

Hi Sunny and everyone,

What if I wanted to exclude the startdate and enddate from the list?

[sunny_talwar]

May be this:

Table:

LOAD OrderID,

  Value,

  StartDate,

  EndDate,

  Date(StartDate + IterNo()) as Date

While Date(StartDate + IterNo()) < EndDate;

LOAD OrderID,

    Value,

    StartDate,

    EndDate

FROM

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

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

[martinpohl]

see attached