Solved: Including days between 2 dates - Qlik Community

Report Inappropriate Content · ‎2011-08-09

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

swuehl · ‎2011-08-21

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

View solution in original post

Report Inappropriate Content · ‎2011-08-09

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)

Report Inappropriate Content · ‎2011-08-09

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?

swuehl · ‎2011-08-09

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

swuehl · ‎2011-08-09

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

Report Inappropriate Content · ‎2011-08-10

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

swuehl · ‎2011-08-10

Please have a look at my attached document.

Depending on your requirements, it may work as shown.

Report Inappropriate Content · ‎2011-08-10

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,

swuehl · ‎2011-08-10

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?

SunilChauhan · ‎2011-08-10

take a resident of table and

in resident

write

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

Sunil Chauhan