Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Link between date and interval

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.

2 Replies
Not applicable

Re: Link between date and interval

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

Specifically:

IntervalMatch (Calendar_Date)

LOAD REGFACTDateFrom, REGFACTDateTo

RESIDENT

Labor_Rate

;

-Rob

Community Browser