Including days between 2 dates

[#1]

Hi All, I'm relatively new to Qlikview. I've got a table that has data populated by date. I need to extract the dates for the dates between the check_in and check_out columns. How would one go about doing this. Below is the code for the table in question:  

[SURVEY RESPONDENTS]:
SQL SELECT agent as "Agent",
annivesary as "Anniversary",
"batch_number",
bookingsource as "Booking Source",
"check_in" as "Check In",
"check_out" as "Check Out",
Datediff(DD,check_in,check_out) as "No. of Days Spent",
Year(check_in) as Years,
Month(check_in) as Month,
Day(check_in) as Day,
"client_email" as "Guest E-mail",
"client_id" as "Client ID",
"client_title" as "Guest Title",
clientprofileid,
company as "Guest Company",
completed as "Completed",
country as "Country",
"currency" as " Currency",
"date_completed" as "Date Survey Completed",
"date_entered" as "Guest Date Entered",
dateofbirth as "Date of Birth",
"email_sent" as " E-Mail Sent",
"first_name" as "First Name",
"global_client_id",
"last_name" as "Surname",
"first_name" +' '+ "last_name" as "Guest Name",
loyaltynumber as "Loyalty Number",
loyaltytype as "Loyalty Type",
mealplan as "Meal Plan",
mobile as "Mobile",
"phone_number" as "Phone Number",
"property_id" as "Property ID",
rate as "Amt Paid(KSH)",
"reservation_id" as "Reservation ID",
"room_number" as "Room Number",
"room_type" as "Room Type",
VIP
FROM "Guest".dbo."survey_respondents";

[#2]

Hi Stefan, i need to compute statistics for a hospitality. Whereby they supply us with check_in and check_out dates. Example check_in :2011/07/01   check_out:2011/07/06   So a guest could of had checked in on the 2011/06/30 and check out on 2011/07/03. This guest should be included in my calculation for occupancy for a day(in this case the 2011/07/02). So if i just make use of check_in and check_out dates, this guest will not be featured in the calculation. Whereas if a take the dates between the check_in and check_out columns, the calculation would work perfectly.In SQL i can do this by the following:

---ONCE OFF
CREATE FUNCTION GET_TIMESTAMP(@DT DATETIME)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF( S, '19700101', @DT );
END

---this is the correct script

SELECT * FROM dbo.survey_respondents
WHERE
dbo.GET_TIMESTAMP(check_in) between dbo.GET_TIMESTAMP('2011/07/01') and dbo.GET_TIMESTAMP('2011/07/06')
OR dbo.GET_TIMESTAMP(check_out) between dbo.GET_TIMESTAMP('2011/07/01') and dbo.GET_TIMESTAMP('2011/07/06')
ORDER BY check_in ASC

---Incorrect Script that excludes the guest that checkin on the 2011/06/30 and checkout on the 2011/07/03

select *
from survey_respondents
where convert(varchar,check_in,111) = '2011/07/01'
and CONVERT(varchar,check_out,111) = '2011/07/06'

[swuehl]

In this case, I would just compare the actual date 2011/07/02 with the check_in and check_out

for example sum all guests for a given actual_date:

=sum(if(actual_date <= check_out and actual_date  >= check_in, 1,0))

Does this sound feasible?

[#4]

Hi Stefan, sorry for the late response. The above mentioned does not work, but if i could write the syntax that would say :  

=sum(if([Check In] IN (concat(Date(ValueLoop([Check In]+1,[Check Out]-1,1)),',',)),1,0))

This might work, but how does one write an IN statement in QlikView?

[swuehl]

Hi,

I am a bit confused:

the value loop will return values for the time period inbetween Check in and check out, but excluding the both dates.

So check in date will never be part of that list, right? As I read it, that's what your if() is checking.

It would be easiest if you could post a small app here (upload available in advance editor), so someone could work with you on your data (could be sample data, no need for your real customer data, of course).

As I understood, you have a calendar, and check in dates and check out dates. For a given date of the calendar (or some other event date, like "SpecialOfferDate"), you would like to check if a customer was checked in.

So, in the table, I would still try customer as dimension and then as only expression (for example new years day):

=if(check_in <= '01/01/2011' and check_out >= '01/01/2011',1,0)

Given that rows with zero values are  not shown, you should get a table with all customers that stayed around new year 2011.

(hadling dates might be a bit tricky though, you have to take care that date formats exactely match when comparing values, or use numerical representation instead).

Regards,

Stefan

P.S. Back to the valueloop thing, I think it will not work in a table context, only if you have limited your selection to one check_in / check_out pair. valueLoop is not really made for things like that. So I haven't followed that idea further

edited by swuehl

[#6]

Hi Stefan, my apologies for the late reply, was off for a couple of days. Anyway i tried using the following formula

=count(if([Check In] <= [Check Out] and [Check Out] >= [Check In],[Guest Name],0))

but it does not work. If i selected as my check-in 2011/08/01 and check-out 2011/08/10. the result is only guests whom have checked in for those dates, and not the dates between check-in and check-out. Any suggestions on this one?

[swuehl]

Hi,

I assume your above if condition is always true, since your checking (twice) if check in date is prior or equal check out date.

That's not what you want. You need a date field with values for all dates within the period you are interested in.

If you currently only have check in and check out dates, create a master calendar (search the forum, there are lot of threads and example apps).

This master calendar could not be associated to other tables, just for time period selection.

Then you could use

=count(if([Check In] >= min(MasterDate) and [Check Out] <= max(MasterDate) ,[Guest Name],0))

If your master calendar is associated to your check in and check out dates, above will probably not work, you might need a set expression:

= count({<[Check In]= {">=$(=min(MasterDate))"}, [Check Out]={"<=$(=max(MasterDate))"} >} [Guest Name])

to count all Guests with a visit in the selected period.

Hope this helps,

Stefan

P.S. If you could post a sample app here, I think that could ease helping you.

[#8]

Hi Stefan, thanks again for the assistance. I did as you suggested above, but i think somewhere in my linking the calender to my data, something went wrong. Because I get the correct total number of guests,but when clicking on any chart, the data is horribly wrong. I've attached a sample copy of the model(no calender links in this one), can you kindly guide me to the correct way of displaying the data with the check_in and check_out in mind. The attached version when you open it has filters on it, basically for 2011 year, the 7th month, and check_in and check_out days from the 1st to the 7th. The result of the total Guests should be 1202 and not 3 as displayed in the model. Can you guide me to how this will be made possible. PS : I've removed my incorrect calender settings and reverted it back to the original state. Thanks again for all your help.

[swuehl · Accepted solution]

Hi,

I tried to create a sample app, please see attached.

In tab Proposal, you will see that I selected Jul 2011 in the calendar (I replaced all Date objects with calendar date objects. I noticed, that in your original version, you had kind of mix between check In and check out dates when creating Month and year, I think you should remove these fields or name them explicitely as check_out_Month etc.)

For July 2011, I get 1202 guests staying at one of the hotels, totalling 3066 days of visit. Please note, that I included check_in and check_out day  in my calculation for visit days, where e.g. the field no. of days spent is calculated as check_out - check_in Date, resulting in zero days if the dates are the same and one day if check_out is the day after check_in (where I count these visits as 1 resp 2 days.)

In your original version, you got 3 guests, but you selected explicitely check_in on July 1st and check_out July 7th, which is not giving what you intended, I think.

So please have a look at my proposal, I can explain some more, if needed.

Regards,

Stefan

[#10]

Hi Stefan, It is working quite well now. Thanks for all your assistance, it's duly appreciated.