Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Specifically:
IntervalMatch (Calendar_Date)
LOAD REGFACTDateFrom, REGFACTDateTo
RESIDENT
Labor_Rate
;
-Rob