topic Re: Re: Insert rows between two date ranges in QlikView

Insert rows between two date ranges

senarath — Wed, 20 Aug 2014 10:31:53 GMT

Hi ,

I have a requirement as below and please advise me on this.

Person Name XYZ

Hotel Check In Date 01/Aug/2013

Hotel Check Out Date 05/Aug/2013

When I search by giving date as 03/Aug/2013 in this particular hotel, I should see person XYZ is already a inhouse guest.

Inserting rows from 01 to 04 as Check In Date would work and if this is ok, how to handle this.

So I guess something like above or what you have mentioned would work but my confusion is that how to handle between two dates and it is not a series.

thanks,

Senarath

Re: Insert rows between two date ranges

jonathandienst — Wed, 20 Aug 2014 10:35:04 GMT

Read Henrics blg post on the issue:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/09/02/loops-in-the-script

Re: Insert rows between two date ranges

Vegar — Wed, 20 Aug 2014 10:36:40 GMT

An alternative solution is to use the IntervalMatch-statement. Combining your transactions with a calendar using interval match could solve your problem.

The benefit is that you dont need to create additional transaction rows.

Re: Insert rows between two date ranges

tresB — Wed, 20 Aug 2014 10:44:41 GMT

A simple example:

Load

project,

[start date]+IterNo() as Date

While [start date]+iterno()<=[finish date];

Load * From <>;

Re: Insert rows between two date ranges

MarcoWedel — Wed, 20 Aug 2014 10:57:16 GMT

tabCheckIns:
LOAD * Inline [
Guest Name,Hotel Check In Date,Hotel Check Out Date
XYZ,01/Aug/2013,05/Aug/2013
ABC,10/Sep/2013,15/Oct/2013
BCD,03/Aug/2013,15/Aug/2013
CDE,01/Oct/2013,03/Nov/2013
DEF,30/Sep/2013,05/Oct/2013
];

tabCalendar:
LOAD *,
    WeekName(Date) as Week,
    Month(Date) as Month,
    QuarterName(Date) as Quarter,
    Year(Date) as Year;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Date(Min([Hotel Check In Date])) as MinDate,
    Date(Max([Hotel Check Out Date])) as MaxDate
Resident tabCheckIns;

IntervalMatch (Date)
LOAD [Hotel Check In Date], [Hotel Check Out Date]
Resident tabCheckIns;

Re: Insert rows between two date ranges

senarath — Wed, 20 Aug 2014 11:13:37 GMT

Hi Marco,

When I select Date 04/Aug/2013 in the calender, both customers should get selected.i.e I want to know number of in-house guests in a particular day.

Would it work under your scenario ?

Thanks

Re: Re: Insert rows between two date ranges

Vegar — Wed, 20 Aug 2014 11:41:39 GMT

Yes, you will gett two guests associated with aug 4.

See attached file qvw

Re: Re: Insert rows between two date ranges

senarath — Wed, 20 Aug 2014 11:46:29 GMT

Many thanks Vegar

Re: Re: Insert rows between two date ranges

senarath — Wed, 20 Aug 2014 11:47:27 GMT

Many thanks Marco

Re: Re: Insert rows between two date ranges

MarcoWedel — Wed, 20 Aug 2014 13:42:39 GMT

Yes, this works:

hope you find this helpful.

regards

Marco

Re: Re: Insert rows between two date ranges

MarcoWedel — Wed, 20 Aug 2014 16:30:20 GMT

You're welcome

Regards

Marco