Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Insert rows between two date ranges

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

A simple example:

Load

      project,

      [start date]+IterNo() as Date

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

Load * From <>;

View solution in original post

10 Replies
Highlighted

Read Henrics blg post on the issue:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Partner
Partner

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
MVP
MVP

A simple example:

Load

      project,

      [start date]+IterNo() as Date

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

Load * From <>;

View solution in original post

Highlighted

QlikCommunity_Thread_130082_Pic1.JPG.jpg

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;

Highlighted
Creator III
Creator III

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

Highlighted
Partner
Partner

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

See attached file qvw

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator III
Creator III

Many thanks Vegar

Highlighted
Creator III
Creator III

Many thanks Marco

Highlighted

Yes, this works:

QlikCommunity_Thread_130082_Pic2.JPG.jpg

hope you find this helpful.

regards

Marco