topic Re: Generate all months (not month difference) between two dates in a resultset in QlikView

Generate all months (not month difference) between two dates in a resultset

Wed, 01 Apr 2015 14:26:43 GMT

Hi,

I have a requirement to generate all the months (not just the month difference) between two dates from a list of records.

For example: My data would look like:

Site	Start Date	End Date
A	3/21/2014	6/25/2014
B	6/21/2014	8/30/2014
C	9/12/2014	2/3/2015

And my result should look like:

A	Mar-14
A	Apr-14
A	May-14
A	Jun-14
B	Jun-14
B	Jul-14
B	Aug-14
C	Sep-14
C	Oct-14
C	Nov-14
C	Dec-14
C	Jan-15
C	Feb-15

Any help would be greatly appreciated.

Thanks,

Malai

Re: Generate all months (not month difference) between two dates in a resultset

hic — Wed, 01 Apr 2015 14:36:13 GMT

I would do a

Load

Date(AddMonths(MonthStart(StartDate),IterNo()-1),'MMM-YY') as Month,

Site,

StartDate,

EndDate

From ...

While AddMonths(MonthStart(StartDate),IterNo()-1) <= MonthStart(EndDate);

HIC

Re: Generate all months (not month difference) between two dates in a resultset

ramoncova06 — Wed, 01 Apr 2015 14:53:27 GMT

you can also try with intervalmatch

IntervalMatch

The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.

It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points (Time in the example below) must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point and interval.

The intervals may be overlapping and the discrete values will be linked to all matching intervals.

The general syntax is:

intervalmatch (matchfield) (loadstatement | selectstatement )

matchfield is the field containing the discrete numeric values to be linked to intervals.

loadstatement or selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).

There is also an extended syntax of IntervalMatch including one or several additional key fields. See IntervalMatch (Extended Syntax).

Example:

In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.

OrderLog
Start	End	Order
01:00	03:35	A
02:30	07:58	B
03:04	10:27	C
07:23	11:43	D

EventLog
Time	Event	Comment
00:00	0	Start of shift 1
01:18	1	Line stop
02:23	2	Line restart 50%
04:15	3	Line speed 100%
08:00	4	Start of shift 2
11:43	5	End of production

First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:

OrderLog:

LOAD * INLINE [

Start, End, Order

01:00, 03:35, A

02:30, 07:58, B

03:04, 10:27, C

07:23, 11:43, D

];

EventLog:

LOAD * INLINE [

Time, Event, Comment

00:00, 0, Start of shift 1

01:18, 1, Line stop

02:23, 2, Line restart 50%

04:15, 3, Line speed 100%

08:00, 4, Start of shift 2

11:43, 5, End of production

];

IntervalMatch (Time) LOAD Start, End Resident OrderLog;

The following table box can now be created in QlikView:

Tablebox
Time	Event	Comment	Order	Start	End
00:00	0	Start of shift 1	-	-	-
01:18	1	Line stop	A	01:00	03:35
02:23	2	Line restart 50%	A	01:00	03:35
04:15	3	Line speed 100%	B	02:30	07:58
04:15	3	Line speed 100%	C	03:04	10:27
08:00	4	Start of shift 2	C	03:04	10:27
08:00	4	Start of shift 2	D	07:23	11:43
11:43	5	End of production	D	07:23	11:43

Back to Script Statements and Keywords.

QlikView 11.20 SR6

Re: Generate all months (not month difference) between two dates in a resultset

Wed, 01 Apr 2015 18:42:24 GMT

Hi Henric,

That worked. Thanks a lot!

-Malai

Re: Generate all months (not month difference) between two dates in a resultset

Wed, 01 Apr 2015 18:43:46 GMT

Hi Ramon,

That was really helpful. I was able to achieve this using Interval Match as well. In this approach I had to use Interval match, few joins and distinct loads to achieve this. Considering Interval Match resource usage and few more joins and distinct load, HIC's suggestion will be better right?

*Attaching the file where I was able to achieve this using IntervalMatch. May be a better approach is available using IntervalMatch but this is what I arrived at.

- Malai

Re: Generate all months (not month difference) between two dates in a resultset

Anonymous — Wed, 01 Feb 2017 15:25:09 GMT

Hi Henric,

I am trying to do a similar thing: https://community.qlik.com/message/1203899?et=watches.email.thread#1203899

Do I need to write your code in a separate table?

Quotes:

LOAD

distinct [Elo Project Id] as [Elo Project ID],

[Quote number],

[Quote type],

Approval_Stage,

date#((Date([Quote create date],'MM/DD/YYYY')),'MM/DD/YYYY')as [Quote create date],

[Requested price LC],

Num#([Requested price LC])*num#(Quantity) as [Requested price LC_quantity],

Date(Subscription_Start_Date__c,'MM/DD/YYYY') as [Subscription Start Date],

if(isnull(Date(Subscription_Start_Date__c)), Date(AddMonths((Subscription_Start_Date__c-1),num#(Subscription_Duration__c))), Date(Subscription_End_Date__c,'MM/DD/YYYY') ) as [Subscription End Date],

if(Date#(Today())>=Date(Subscription_End_Date__c),'positive','negative') as Flag1,

num#(Subscription_Duration__c,'######')as subscription,

num#(Quantity,'######') as quote_quantity

FROM

(qvd)

where [Quote type]='SaaS' and len(trim([Elo Project Id]))>0 ;