Add date field for each record that is between two dates (start and finish)

[kolov007]

Help me, please, is there a possibility into QlikView to solve the following problem:

There is database, which includes three fields: Shop, Product, Start date, Date of end.

Key	Shop	Product	Start date	Date of end
1	Shop 1	Product 1	01/08/2015	04/08/2015
2	Shop 2	Product 1	10/08/2015	12/08/2015
3	Shop 3	Product 2	02/08/2015	04/08/2015

Is it possible in QlikView, for each key field (which corresponds to the action of a particular product in a particular shop) to assign all of the dates during which lasted this action. For example, if you select into QlikView date - 03.08.2015, in the list box of active shop would be two records - the shop 1 and 3.

Key	Date
1	01/08/2015
1	02/08/2015
1	03/08/2015
1	04/08/2015
2	10/08/2015
2	11/08/2015
2	12/08/2015
3	02/08/2015
3	03/08/2015
3	04/08/2015

[Oleg_Troyansky]

Yes, check out the syntax of the WHILE clause. Your syntax will look similar to the following:

LOAD

   Key1,

   Key2,

   Key3,

   ...

   Start,

   End,

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

Resident

   MyTable

WHILE

     Date(Start + IterNo() - 1) <= End

;

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[qlikviewwizard]

Hi kolov007,

Please check the attached file. Is this solution you are looking for?

Hi troyansky

I did not understand your solution. Could you provide full script for this.

Thank you.

[qlikviewwizard]

Attachment.

[MarcoWedel]

Search for master calendar and the intervalmatch prefix.

Regards

Marco

[MarcoWedel · Accepted solution]

one solution could be:

table1:
LOAD * FROM [https://community.qlik.com/thread/180208] (html, codepage is 1252, embedded labels, table is @1);

tabCalendar:
LOAD *,
     Day(Date) as Day,
     WeekDay(Date) as WeekDay,
     Week(Date) as Week,
     WeekName(Date) as WeekName,
     WeekYear(Date) as WeekYear,
     Month(Date) as Month,
     MonthName(Date) as MonthName,
     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
     QuarterName(Date) as QuarterName,
     Year(Date) as Year;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Min([Start date]) as MinDate,
     Max([Date of end]) as MaxDate
Resident table1;

tabLink:
IntervalMatch(Date)
LOAD [Start date], 
     [Date of end]
Resident table1;

hope this helps

regards

Marco