# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Contributor III

## ¿IntervalMatch? to calculate an occupancy rate

Hi

I have hundreds of records of a giant restaurant mining camp. Everytime somebody eats, a new record is generated.

Example:

25-01-2015  Name1  Location1   Food1

25-01-2015  Name2  Location2   Food1

25-01-2015  Name3  Location1   Food1

25-01-2015  Name4  Location1   Food1

25-01-2015  Name5  Location2   Food1

25-01-2015  Name6  Location1   Food1

....

And in another place, I have created a table with the capacity of each location:

15-07-2014 Location1     40

15-09-2014 Location1     65

15-12-2014 Location1     80

10-01-2014 Location2     30

So what I need is to know how many times each restaurant has been used his full capacity by day, considering the capacities in each date range.

So if in 25-01-2015  there are 300 records, the restaurant has been used 300/80 = 3,75

So if in 25-09-2015  there are 300 records, the restaurant has been used 300/65 = 4,61

Thanks!!

Tags (1)
5 Replies
Honored Contributor II

## Re: ¿IntervalMatch? to calculate an occupancy rate

So you wish to have the total customers per day/location measured against the varying capacity in certain periods?

Peter

MVP

## Re: ¿IntervalMatch? to calculate an occupancy rate

I have used Inline version but the same you will get using your data source:

T1:

[

Date,Name,Location,Food

25-01-2015,  Name1,  Location1,   Food1

25-01-2015,  Name2,  Location2 ,  Food1

25-01-2015,  Name3,  Location1  , Food1

25-01-2015,  Name4,  Location1  , Food1

25-01-2015,  Name5,  Location2   ,Food1

25-01-2015,  Name6 , Location1   ,Food1

25-01-2015,  Name7,  Location1,   Food1

15-07-2015,  Name8,  Location1 ,  Food1

25-01-2015,  Name9,  Location1  , Food1

25-01-2015,  Name10,  Location1  , Food1

25-01-2015,  Name11,  Location2   ,Food1

25-01-2015,  Name12 , Location1   ,Food1

];

T2:

[

Date,Location,Capacity

15-07-2015, Location1,   4

15-09-2014, Location1,   6

25-01-2015, Location1,   8

10-01-2014, Location2,   3

]

MVP

## Re: ¿IntervalMatch? to calculate an occupancy rate

Try this example. This uses Interval Match to link the Capacity to the respective Date Range for each Location.

Contributor III

## Re: ¿IntervalMatch? to calculate an occupancy rate

Yes Peter, That's what I want!

By the way.. as you write it, sounds a lot more than my text hehe

MVP

## Re: ¿IntervalMatch? to calculate an occupancy rate

Hi,

one solution could be:

```//test data generation
tabRestOrders:
'Name'&Ceil(Rand()*5) as Name,
'Location'&Ceil(Rand()*5) as Location,
'Food'&Ceil(Rand()*5) as Food
AutoGenerate 100000;

tabCapacityTemp:
'Location'&Ceil(Rand()*5) as Location,
Ceil(Rand()*91)+9 as Capacity
AutoGenerate 50;
'Location'&RecNo() as Location,
Ceil(Rand()*91)+9 as Capacity
AutoGenerate 5;

//interval generation
tabCapacity:
AutoNumberHash128(MinCapDate, MaxCapDate) as CapIntervID;
Capacity,
CapacityDate as MinCapDate,
If(Location=Previous(Location),Date(Previous(CapacityDate)-1),MakeDate(2100)) as MaxCapDate
Resident tabCapacityTemp
Order By Location, CapacityDate desc;

DROP Table tabCapacityTemp;

//interval match
Left Join (tabRestOrders)
IntervalMatch(Date, Location)
Resident tabCapacity;

Left Join (tabRestOrders)
MinCapDate,
MaxCapDate,
AutoNumberHash128(MinCapDate, MaxCapDate) as CapIntervID
Resident tabRestOrders;

DROP Fields MinCapDate, MaxCapDate From tabRestOrders;

//calendar
tabCalendar:
Hour(Date) as Hour,
Day(Date) as Day,
WeekDay(Date) as WeekDay,
Week(Date) as Week,
WeekName(Date) as WeekName,
Month(Date) as Month,
MonthName(Date) as MonthName,
Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,
QuarterName(Date) as QuarterName,
Year(Date) as Year,
WeekYear(Date) as WeekYear;