# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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
Highlighted
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

Highlighted
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

]

Highlighted
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.

Highlighted
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

Highlighted
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;

While MinDate+IterNo()-1<=MaxDate;