topic Re: Including days between 2 dates in QlikView

Including days between 2 dates

Tue, 09 Aug 2011 14:34:54 GMT

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

Including days between 2 dates

Tue, 09 Aug 2011 15:19:09 GMT

Are you after number of days between the two in which case you can either do Check_In-Check_Out or if looking for number of working days NetworkDays(Check_In,Check_Out)

Including days between 2 dates

Tue, 09 Aug 2011 19:33:53 GMT

Hi There, thanks for the prompt feedback. What I'm actually looking for is the actual dates between the check_in and check_out columns. Example check_in is 2011/08/01 and check_out is 2011/08/05. The result of the function should display 2011/08/02, 2011/08/03, 2011/08/04. Is this possible?

Including days between 2 dates

swuehl — Tue, 09 Aug 2011 23:29:26 GMT

Hi,

maybe your looking for something like:

=concat(Date(ValueLoop(check_in,check_out,1)),',',)

where check_in and check_out should be numerical / dates.

I just see that you want only the dates inbetween, could be:

=concat(Date(ValueLoop(check_in+1,check_out-1,1)),',',)

but haven't tested (and tried if dates are the same or sequential).

Regards,

Stefan

Including days between 2 dates

swuehl — Tue, 09 Aug 2011 23:33:29 GMT

Aargh, I just tried if start>end of valueloop.

Won't handle properly, better to check and take care of before calling the function.

Stefan

Including days between 2 dates

Wed, 10 Aug 2011 11:43:24 GMT

Hi Stefan, thanks for the feedback. But it still does not work. Any other suggestions.

Re: Including days between 2 dates

swuehl — Wed, 10 Aug 2011 12:01:54 GMT

Please have a look at my attached document.

Depending on your requirements, it may work as shown.

Re: Including days between 2 dates

Wed, 10 Aug 2011 12:49:24 GMT

Hi Stefan, thanks for the model. It works perfectly. My next step would be to query the result from this text box. Alternatively can i make use of the formula in an expression, whereby I need to say that the check_in is between =concat(Date(ValueLoop(check_in+1,check_out-1,1)),',',). Is this possible? Sorry for bugging you. Thanks,

Re: Including days between 2 dates

swuehl — Wed, 10 Aug 2011 12:56:43 GMT

The valueloop functions seems to have some severe limitations for use under normal conditions.

Maybe you could describe what you want to achieve, what your business case is? There might be a better, simpler solution then.

I did not understand your last requirement, where the check_on is between the date list? Could you give an example, please?

Including days between 2 dates

SunilChauhan — Wed, 10 Aug 2011 13:02:53 GMT

take a resident of table and

in resident

write

( check_in-check_out) as [No. of Days Spent]

Re: Including days between 2 dates

Wed, 10 Aug 2011 13:12:13 GMT

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'

Re: Including days between 2 dates

swuehl — Wed, 10 Aug 2011 13:20:49 GMT

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?

Re: Including days between 2 dates

Thu, 11 Aug 2011 08:11:32 GMT

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?

Re: Including days between 2 dates

swuehl — Thu, 11 Aug 2011 22:43:47 GMT

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

Including days between 2 dates

Mon, 15 Aug 2011 10:53:47 GMT

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?

Including days between 2 dates

swuehl — Mon, 15 Aug 2011 11:56:27 GMT

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.

Re: Including days between 2 dates

Tue, 16 Aug 2011 11:28:52 GMT

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.

Re: Including days between 2 dates

swuehl — Sun, 21 Aug 2011 21:37:06 GMT

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

Including days between 2 dates

Mon, 22 Aug 2011 13:11:28 GMT

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