Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

count - Flag if date is between start date and end date

Hello,

Hope all is well with everyone.

We have two tables - master calendar and vehicle activity table

The vehicle activity table has vehicle number, start and end date

VEHICLE:

LOAD * INLINE

[VEHICLE_NO, STARTDATE, ENDDATE

VIN1, 01/01/2017, 01/05,2017

VIN2, 01/10/2017, 01/15/2017

VIN3, 01/01/2017, 01/15/2017

VIN4, 01/01/2017, 01/20/2017

VIN5, 01/01/2017, 01/25/2017

VIN6, 01/01/2017, 01/30/2017

We want to find the count of how many vehicles are available on certain date depending on the selection (year, month, day)

for example if we select 01/08/2017 then it should give the following count

VIN1, 01/01/2017, 01/05,2017 - NOT AVAILABLE because 01/08/2017 is after the ENDDATE of 01/05/2017

VIN2, 01/10/2017, 01/15/2017 - NOT AVAILABLE as 01/08/2017 is before the STARTDATE of 01/10/2017

VIN3, 01/01/2017, 01/15/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN4, 01/01/2017, 01/20/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN5, 01/01/2017, 01/25/2017 - AVAILABLE as 01/08/2017 is between START and END DATE

VIN6, 01/01/2017, 01/30/2017 - AVAILABLE as 01/08/2017 is between START and END DATE


So count of vehicles on 01/08/2017 is going to be 4

Will appreciate if someone can assist us.

Thanks.

1 Solution

Accepted Solutions

Re: count - Flag if date is between start date and end date

Try this way and generate missing rows in between Start Date and end Date

VEHICLE:

LOAD

Date( num(STARTDATE) +IterNo() -1) as Date,

VEHICLE_NO,Date(Date#( STARTDATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS STARTDATE,Date( Date#( ENDDATE,'MM/DD/YYYY'),'MM/DD/YYYY') as ENDDATE INLINE

[

VEHICLE_NO, STARTDATE, ENDDATE

VIN1, 01/01/2017, 01/05/2017

VIN2, 01/10/2017, 01/15/2017

VIN3, 01/01/2017, 01/15/2017

VIN4, 01/01/2017, 01/20/2017

VIN5, 01/01/2017, 01/25/2017

VIN6, 01/01/2017, 01/30/2017

] While Date( Num(STARTDATE) +IterNo() -1) <= ENDDATE;


And on the UI just select the date you want and on any text object show the count of the Vehicle.


Output:-

OP4.PNG

View solution in original post

2 Replies

Re: count - Flag if date is between start date and end date

Try this way and generate missing rows in between Start Date and end Date

VEHICLE:

LOAD

Date( num(STARTDATE) +IterNo() -1) as Date,

VEHICLE_NO,Date(Date#( STARTDATE,'MM/DD/YYYY'),'MM/DD/YYYY') AS STARTDATE,Date( Date#( ENDDATE,'MM/DD/YYYY'),'MM/DD/YYYY') as ENDDATE INLINE

[

VEHICLE_NO, STARTDATE, ENDDATE

VIN1, 01/01/2017, 01/05/2017

VIN2, 01/10/2017, 01/15/2017

VIN3, 01/01/2017, 01/15/2017

VIN4, 01/01/2017, 01/20/2017

VIN5, 01/01/2017, 01/25/2017

VIN6, 01/01/2017, 01/30/2017

] While Date( Num(STARTDATE) +IterNo() -1) <= ENDDATE;


And on the UI just select the date you want and on any text object show the count of the Vehicle.


Output:-

OP4.PNG

View solution in original post

Partner
Partner

Re: count - Flag if date is between start date and end date

‌Thanks a lot. This worked perfectly.