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";
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.
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)
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?
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,
