Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Interval Match?

Hi,
I have the following two tables:
Tab1:
load * inline
[StartDate, EndDate, EmpPeriod
10000, 20000, FirstYear
20001, 30000, SecondYear
30001, 40000, ThirdYear
]
;

Tab2:
load * inline
[SDate, EDate, EmpName
15000, 16000, Tony
22000, 29999, Mark
19999, 25000, John
32000, 39000, Ken
10023, 39997, Frank
]
;
The SDate and EDate would determine the EmpPeriod of EmpName. I tried Interval Match but maybe I am doing something wrong. I can do for either SDate or EDate between StartDate and EndDate, but how do I check if SDate AND EDate are between StartDate and EndDate?
The result should be:
EmpNameEmpPeriod
TonyFirstYear
MarkSecondYear
JohnFirstYear
JohnSecondYear
KenThirdYear
FrankFirstYear
FrankSecondYear
Frank
ThirdYear
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

Add the following lines after your two inline tables:

// ========== Find lowest and highest date ==========

AllDates:

Load  StartDate as TempDate resident Tab1;

Load  EndDate as TempDate resident Tab1;

Load  SDate as TempDate resident Tab2;

Load  EDate as TempDate resident Tab2;

MinMaxDate:

Load Min(TempDate) as MinDate, Max(TempDate) as MaxDate resident AllDates;

Let vMinDate = peek('MinDate',-1,'MinMaxDate') -1 ;

Let vMaxDate = peek('MaxDate',-1,'MinMaxDate') ;

Drop Tables AllDates, MinMaxDate ;

// ========== Generate all Dates ==========

Dates:

Load Date($(vMinDate) + recno()) as Date autogenerate $(vMaxDate) - $(vMinDate) ;

// ========== Perform two IntervalMatch:es ==========

IntervalMatch1:

IntervalMatch (Date) Load StartDate, EndDate resident Tab1;

IntervalMatch2:

IntervalMatch (Date) Load SDate, EDate resident Tab2;

View solution in original post

10 Replies
hic
Former Employee
Former Employee

Add the following lines after your two inline tables:

// ========== Find lowest and highest date ==========

AllDates:

Load  StartDate as TempDate resident Tab1;

Load  EndDate as TempDate resident Tab1;

Load  SDate as TempDate resident Tab2;

Load  EDate as TempDate resident Tab2;

MinMaxDate:

Load Min(TempDate) as MinDate, Max(TempDate) as MaxDate resident AllDates;

Let vMinDate = peek('MinDate',-1,'MinMaxDate') -1 ;

Let vMaxDate = peek('MaxDate',-1,'MinMaxDate') ;

Drop Tables AllDates, MinMaxDate ;

// ========== Generate all Dates ==========

Dates:

Load Date($(vMinDate) + recno()) as Date autogenerate $(vMaxDate) - $(vMinDate) ;

// ========== Perform two IntervalMatch:es ==========

IntervalMatch1:

IntervalMatch (Date) Load StartDate, EndDate resident Tab1;

IntervalMatch2:

IntervalMatch (Date) Load SDate, EDate resident Tab2;

Not applicable
Author

Thank You Henric, That works fine!

I was about to create a view in sql. But now I will test your code with the catual data.

Regards

Raj

Not applicable
Author

Hi Henrik,

I was trying to remove the synthetic keys using joins but the actual document fails to execute. Any idea how to solve the problem?

Regards

Raj

hic
Former Employee
Former Employee

I would keep the synthetic keys. Synthetic keys are not always bad and in this case a data model with syntetic keys is in fact the most optimal one.

But if you still want to remove them, then there are several ways. You could e.g. join the two intervalmatch tables onto their source tables (but this denormalizes the model)

Left join (Tab1) IntervalMatch (Date) Load StartDate, EndDate resident Tab1;

Left join (Tab2) IntervalMatch (Date) Load SDate, EDate resident Tab2;

/HIC

Not applicable
Author

Hi Henric,

When I use:

Left join (Tab1) IntervalMatch (Date) Load StartDate, EndDate resident Tab1;

Left join (Tab2) IntervalMatch (Date) Load SDate, EDate resident Tab2;

in the example I had given above. It works fine.

But with the actual document which have many records it fails to execute. I think I will keep the synthetic keys for now.

Regards

Raj

Not applicable
Author

Hello Sir,

You've helped me before also with intervalmatch

Need ur help again.

I've a table with Project Nos., StartdateProject,EnddateProject & Revenue

I need to a create a Calender so that on selecting a particular Year , all the projects starting or ending in this Year are displayed.

Moreover, on selecting the Quarters (Q1 or Q2 or Q3 or Q4) the Revenue should be shown only for that Quarter.

For eg. if a project has Yearly Revenue of 1000 & the project runs from 1 Jan, 2015 to 31 Dec, 2015

then on Selecting Q1, Revenue displayed should be 1000/4(=250).

Please help

Regards

hic
Former Employee
Former Employee

I would probably not use intervalmatch for this. You need to create a table where you have exactly one record per combination of project and quarter. You can do this using a while-loop where you create several records per project. Each new record will contain the revenue for that specific quarter - and not more.

See attachment.

HIC

Not applicable
Author

Sir,

i don't have words to explain my joy, i would still say THANK YOU.

it really workd well, but my mistake i gave only a part of the problem.

The thing is that i need to create a bar chart having dimensions as two cyclic groups of Dim1(Project No.,etc.) & Dim2(Year,Month,Quarter,QuarterYear,Month,MonthYear).

Now i asked for the Quarter as i thought that rest i would create by myself bcos its only abt using Month(SomeDate) if u had Intervalmatch.

But in this case, do i need to calculate the Distributed Revenue each for Year,Month,Quarter,QuarterYear,Month,MonthYear)

but then i have to call them Quarterly_Dist_Revenue,Yearly_Dist_Revenue,Monthly_Dist_Revenue. But i need to have only one expression.

Explaining further, if a project starts on 1st Dec,2014 and ends 31 Dec 2015 then, for Year 2014,

Revenue in 2014=(Total Revenue/no. of days of Project)* no. of days in 2014 for which project runs

Revenue in 2015=(Total Revenue/no. of days of Project)* no. of days in 2015 for which project runs

eg. Total Revenue=10000, Project Days=31+365=396

So, Revenue in 2014=(10000/396)*31=783

In short the Revenue is to be evenly spread across Year,Month,Quarter,QuarterYear,Month & MonthYear

Thanks & Regards

hic
Former Employee
Former Employee

No problem. In fact, this makes the script slightly simper. But I would still use the the while-loop instead of intervalmatch.

It is just a matter of distributing the Revenue over the days and not over the quarters. See attachment. However you will need a master calendar to define months, quarters, etc.

HIC