Insert rows between two date ranges

[senarath]

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

[jonathandienst]

Read Henrics blg post on the issue:

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

[Vegar]

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.

[tresesco · Accepted solution]

A simple example:

Load

      project,

      [start date]+IterNo() as Date

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

Load * From <>;

[MarcoWedel]

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;

[senarath]

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

[Vegar]

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

See attached file qvw

[senarath]

Many thanks Vegar

[senarath]

Many thanks Marco

[MarcoWedel]

Yes, this works:

hope you find this helpful.

regards

Marco