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

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

kolov007 — Fri, 11 Sep 2015 17:02:21 GMT

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

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

Oleg_Troyansky — Fri, 11 Sep 2015 21:39:37 GMT

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

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

qlikviewwizard — Sat, 12 Sep 2015 02:36:45 GMT

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.

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

qlikviewwizard — Sat, 12 Sep 2015 02:37:16 GMT

Attachment.

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

MarcoWedel — Sat, 12 Sep 2015 08:42:40 GMT

Search for master calendar and the intervalmatch prefix.

Regards

Marco

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

MarcoWedel — Sat, 12 Sep 2015 22:59:37 GMT

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