Link between date and interval

Tue, 28 Aug 2012 11:17:39 GMT

Hi,

I'm trying to make a link between 2 tables and I don't know how to do that.

The problem is that I have a pair of fields in a table like datefrom and dateto. These 2 dates makes an interval.

I want to link another date from a master calendar this way:

A date from the master calendar will be linked with a register of the other table if the date from the master calendar is in the interval of the other table.

Here is the relevant part of the script

where

REGFACTDateFrom,
REGFACTDateTo,

are the interval and

Calendar_Date

is the other date

Labor_Rate:
LOAD
REGFACTDateFrom,
REGFACTDateTo,
REGFACTRegion AS Link_Data_SuperRegion,
REGFACTType,
REGFACTFactor;

SQL SELECT *
FROM `service_dashboard`.regionfactors;

Con calendario:

LET vDateMin = Num(MakeDate(2010,11,01));
LET vDateMax = Num(date(Today()));

TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
Month (Date (monthstart(TempDate, 0),'DD/MM/YYYY')) AS Month,
Year (Date (yearstart (TempDate, 1, 11),'DD/MM/YYYY')) AS Year,
Num(Month(TempDate)) AS MonthNumber,

'FY'&Mid(Ceil(Year(AddMonths(TempDate,2))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,2))))=1,'0')&Ceil(Month(AddMonths(TempDate,2))) AS FiscalMonth,
'FY'&Mid(Ceil(Year(AddMonths(TempDate,1))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,1))))=1,'0')&Ceil(Month(AddMonths(TempDate,1))) AS PreviousFiscalMonth,
//'FY'&Mid(Ceil(Year(AddMonths(TempDate,0))),3,2)&'M'&if(Len(Ceil(Month(AddMonths(TempDate,0))))=1,'0')&Ceil(Month(AddMonths(TempDate,0))) AS Previous2FiscalMonth,
       'FY'&Mid(Ceil(Year(AddMonths(TempDate,2))),3,2)&'Q'&Ceil(Month(AddMonths(TempDate,2))/3) AS FiscalQuarter,
'FY'&Mid(Ceil(Year(AddMonths(TempDate,1))),3,2)&'Q'&Ceil(Month(AddMonths(TempDate,1))/3) AS PreviousFiscalQuarter,

'Q'&Ceil(Month(AddMonths(TempDate,2))/3) AS Quarter,
TempDate AS Calendar_Date

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

store MasterCalendar into C:\Users\casellag\Gerard\Customer Contribution Margin Report\Qlikview CCMR\Gerard - QV CCMR\CCMR_QVD_DATE.qvd;

drop Table MasterCalendar;

Thanks in advance.

Re: Link between date and interval

Tue, 28 Aug 2012 12:24:06 GMT

You can use the intervalmatch function: generate a list (table) of dates from the minimum REGFACTDateFrom to the max of REGFACTDate

Join this generated table to the table with the intervals in it, by using the intervalmatch() function.

This way, you will fill the intervals with the dates in between. These dates in between can be used to make a relation to the calendar.

NOTE: Using this technique will result in an 'explosion' of data, since you duplicate a lot of rows.

Hope it is a bit clear

Re: Link between date and interval

rwunderlich — Tue, 28 Aug 2012 18:21:12 GMT

Specifically:

IntervalMatch (Calendar_Date)

LOAD REGFACTDateFrom, REGFACTDateTo

RESIDENT

Labor_Rate

;

-Rob

topic Re: Link between date and interval in QlikView

Link between date and interval

Re: Link between date and interval

Re: Link between date and interval