Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
senarath
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
tresesco
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
jonathandienst

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
Vegar
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
tresesco
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

MarcoWedel

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;

senarath
Creator III
Creator III
Author

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
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
senarath
Creator III
Creator III
Author

Many thanks Vegar

senarath
Creator III
Creator III
Author

Many thanks Marco

MarcoWedel

Yes, this works:

QlikCommunity_Thread_130082_Pic2.JPG.jpg

hope you find this helpful.

regards

Marco