how to check date which lies in between two date

[brijeshvma]

Hi All ,

I have a one table in that i have start date and end date . and second table i have where i have one more date ..

I want the dynamic list condition .. it means if user not selected any date so it check , if today date comes in between start date and end

date so its paid else unpaid . but say if user select any date , say user selected 01 jan 2016 . so its start date and end date in which 01 jan date come so those id will be paid else other are unpaid.

It means if user check today with PropertyID are paid and unpaid . and if user select any other period date , so he can know on that date,

Which PropertID are paid and Unpaid...

Kindly help me on the same..

[MarcoWedel]

You could use Intervalmatch ()

Regards

Marco

[brijeshvma]

Hi Macro

I am new in qlikview . Can tell me how to use IntervalMatch()  in my code.

I have qvw as well attached

[brijeshvma]

As well i want to check the date on User Selection whether it comes in start date and end end date

[sunny_talwar]

You can try one of these options:

Start:

LOAD Timestamp(start_datetime) as start_datetime,

     Timestamp(end_datetime)  as end_datetime,

     If(Today() >= start_datetime and Today()<=end_datetime , 'PAID','UNPAID') as CheckPaid,

     entity_id,

     PropertyID

FROM

(qvd)

Lead:

LOAD PropertyID,

  Link_Date

FROM

(qvd);

IntervalMatch:

IntervalMatch (Link_Date)

LOAD start_datetime,

  end_datetime

Resident Start;

or

Start:

LOAD Timestamp(start_datetime) as start_datetime,

     Timestamp(end_datetime)  as end_datetime,

     If(Today() >= start_datetime and Today()<=end_datetime , 'PAID','UNPAID') as CheckPaid,

     entity_id,

     PropertyID

FROM

(qvd);

Lead:

LOAD PropertyID,

  Link_Date

FROM

(qvd);

Left Join (Start)

IntervalMatch (Link_Date)

LOAD start_datetime,

  end_datetime

Resident Start;

Left Join (Start)

LOAD *

Resident Lead;

DROP Table Lead;

[brijeshvma]

Hi Sunny ,

Thanks for your reply ...

but not able to understand your code clearly . say I used your first code

Start:

LOAD Timestamp(start_datetime) as start_datetime,

     Timestamp(end_datetime)  as end_datetime,

     If(Today() >= start_datetime and Today()<=end_datetime , 'PAID','UNPAID') as CheckPaid,

     entity_id,

     PropertyID

FROM

(qvd) ;

Lead:

LOAD PropertyID,

  Link_Date

FROM

(qvd);

IntervalMatch:

IntervalMatch (Link_Date)

LOAD start_datetime as Start,

  end_datetime as End

Resident Start;

Because without rename its gives me Synthetic key as well how i can write expression for condition

when Link_Date comes in between start date and end date its paid either its unpaid .  can you help me to understand this interval match and this solution . Kindly help me ..

[sunny_talwar]

Yes it does create a synthetic key, but it isn't a bad thing in this case. Read the following from HIC's blog (IntervalMatch)

With regards to the condition, I am not sure what condition are you looking for. Can you elaborate?

[brijeshvma]

Yes i was checking HIC this Post only.

Let me elaborate what i need ,

in one table i have link date and another table i have start date and end date these two tables are connected with PropertID , actually in my data model there are multiple table which are connected with Propertid . thats a reason why i try to avoid synthetic key . because each table have several measures . so is there any value problem with synthetic key .

The main reason for this Query is . i need to create one dimension called Checkpaid . and condition is if say user select any date from date filter and if that dates comes in between start date and end date so its paid else its Unpaid ..

Say Example ..

Cust want to check which are the property was paid in jan month and he selected 02/01/2016 . so all Propertyid shows Paid where 02/01/2016 comes in between start date and end date ..

Link Date                StartDate  End Date   CheckPaid

02/01/2016              01/01/2016   31/01/2016  Paid ,

02/01/2016               31/12/2015    01/01/2016  Unpaid

[sunny_talwar]

Seem like the requirement is driven by front end selection of users. If that is the case, then I believe you would need a front end solution for this. Let me see if I can try to implement in the attached sample

[brijeshvma]

But cant we do on script say if we do left join and make the date like

Propertyid           linkdate              startdate      enddate

Projects/10001     01/01/2015        31/12/2015   02/02/2015 

and make one if condition

if (linkdate >=startdate and linkdate<=enddate ,' Paid' , 'Unpaid') as CheckPaid

i tried this .. but giving invalid expression