topic Re: make date field for each record in a range in QlikView

make date field for each record in a range

Fri, 26 May 2017 11:14:59 GMT

Hi i have a table that looks similar to this

Revision	Start Date	End Date
1	02/05/2017	02/08/2017
2	02/07/2017	02/25/2017
3	2/11/2017	05/02/2017
4	03/15/2017	04/01/2017
5	03/30/2017	05/25/2017
6	04/12/2017	05/15/2017

in addition i have a master calendar. What i want to do is create a table with the revision number for each date from Start Date to End Date from the above table so that it will link to my Master Calendar. The result should look like this

Master Date	Revision
02/05/2017	1
02/06/2017	1
02/07/2017	1
02/07/2017	2
02/08/2017	1
02/08/2017	2
02/09/2017	2
02/10/2017	2
02/11/2017	2
02/11/2017	3

Any help here would be greatly appreciated.

Re: make date field for each record in a range

migueldelval — Fri, 26 May 2017 11:16:34 GMT

Hi Ben,

Maybe you can use interval match.

Interval Match Feature/Function

Regards

Miguel del Valle

Re: make date field for each record in a range

sunny_talwar — Fri, 26 May 2017 11:19:24 GMT

Try this with a while loop or IntervalMatch

While loop

Table:

LOAD *,

Date([Start Date] + IterNo() - 1) as [Master Date]

While [Start Date] + IterNo() - 1 <= [End Date];

LOAD * INLINE [

Revision, Start Date, End Date

1, 02/05/2017, 02/08/2017

2, 02/07/2017, 02/25/2017

3, 02/11/2017, 05/02/2017

4, 03/15/2017, 04/01/2017

5, 03/30/2017, 05/25/2017

6, 04/12/2017, 05/15/2017

];

Re: make date field for each record in a range

Anil_Babu_Samineni — Fri, 26 May 2017 11:21:37 GMT

You can use this in preceding load for that inline memory

LOAD *,

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

While [Start Date] + IterNo() - 1 <= [End Date];

PS - It will generate till End Date and adding each next day to Start Date

Re: make date field for each record in a range

Fri, 26 May 2017 12:19:45 GMT

thank you all for the replies. I am trying to use intervalmatch as this seems to be the cleanest and less cumbersome way to do this. i am not able to get my desired results though.

Here is the script i am using:

REVISION:

LOAD * INLINE [

Revision, Start_Date, End_Date

1,2/5/2017,2/8/2017

2,2/7/2017,2/25/2017

3,2/11/2017,5/2/2017

4,3/15/2017,4/1/2017

5,3/30/2017,5/25/2017

6,4/12/2017,5/15/2017

];

MASTER_DATE:

LOAD * INLINE [

MASTER_DATE

2/5/2017

2/6/2017

2/7/2017

2/8/2017

2/9/2017

2/10/2017

2/11/2017

2/12/2017

2/13/2017

2/14/2017

2/15/2017

2/16/2017

2/17/2017

2/18/2017

2/19/2017

2/20/2017

2/21/2017

2/22/2017

2/23/2017

2/24/2017

2/25/2017

2/26/2017

2/27/2017

2/28/2017

3/1/2017

3/2/2017

3/3/2017

3/4/2017

3/5/2017

3/6/2017

3/7/2017

3/8/2017

3/9/2017

3/10/2017

3/11/2017

3/12/2017

3/13/2017

3/14/2017

3/15/2017

3/16/2017

3/17/2017

3/18/2017

3/19/2017

3/20/2017

3/21/2017

3/22/2017

3/23/2017

3/24/2017

3/25/2017

3/26/2017

3/27/2017

3/28/2017

3/29/2017

3/30/2017

3/31/2017

4/1/2017

4/2/2017

4/3/2017

4/4/2017

4/5/2017

4/6/2017

4/7/2017

4/8/2017

4/9/2017

4/10/2017

4/11/2017

4/12/2017

4/13/2017

4/14/2017

4/15/2017

4/16/2017

4/17/2017

4/18/2017

4/19/2017

4/20/2017

4/21/2017

4/22/2017

4/23/2017

4/24/2017

4/25/2017

4/26/2017

4/27/2017

4/28/2017

4/29/2017

4/30/2017

5/1/2017

5/2/2017

5/3/2017

5/4/2017

5/5/2017

5/6/2017

5/7/2017

5/8/2017

5/9/2017

5/10/2017

5/11/2017

5/12/2017

5/13/2017

5/14/2017

5/15/2017

5/16/2017

5/17/2017

];

IntervalMatch(MASTER_DATE)

load distinct Start_Date, End_Date

Resident REVISION;

And my results below:

Re: make date field for each record in a range

sunny_talwar — Fri, 26 May 2017 12:41:40 GMT

You dates are not read as dates by QlikView... make sure you have this in your environmental variables

SET DateFormat='M/D/YYYY';

Re: make date field for each record in a range

MarcoWedel — Mon, 29 May 2017 22:36:15 GMT

Hi,

maybe helpful:

Creating Reference Dates for Intervals

Generating Missing Data In QlikView

regards

Marco