topic Re: how to match date between period in QlikView

how to match date between period

upaliwije — Wed, 21 Feb 2018 09:42:24 GMT

I have QV doc where I want to load data from two tables

In my first I have data ewlating to Policy Details and in the 2nd data set claim details. First I want to load data from policy table and secondly o claim data if a policy exists from the first table and Loss date of claims table should fall between From_date and To_date in policy Table. I have wrtten the script but it does not work. Kindly help

ss:

LOAD * INLINE [

Policy_no, Policy_Date, From_date, To_date, Premium

P1, 1-Jan-17, 1-Jan-17, 31-Dec-17, 25000

P2, 2-Feb-17, 2-Feb-17, 1-Feb-18, 35000

P3, 3-Mar-17, 3-Mar-17, 2-Mar-18, 10000

P4, 4-Apr-17, 4-Apr-17, 3-Apr-18, 40000

P5, 5-May-17, 5-May-17, 4-May-18, 12500

P6, 6-Jun-17, 6-Jun-17, 5-Jun-18, 8000

P7, 7-Jul-17, 7-Jul-17, 6-Jul-18, 11000

P8, 8-Aug-17, 8-Aug-17, 7-Aug-18, 32000

P9, 9-Sep-17, 9-Sep-17, 8-Sep-18, 21000

P10, 10-Oct-17, 10-Oct-17, 9-Oct-18, 14000 ];

Concatenate(ss)

LOAD * INLINE [

Policy_no, Claim_no, Loss_Date, claim

P11, C1, 1-Dec-16, 10000

P2, C2, 2-Feb-17, 4000

P2, C3, 1-Jan-17, 2000

P4, C4, 4-Mar-17, 3500

P4, C5, 5-May-17, 5000

P6, C6, 6-Jun-18, 4000

P7, C7, 7-Jul-17, 1500

]

where exists(Policy_no)

AND Loss_Date>From_date AND Loss_Date<To_date ;

Re: how to match date between period

upaliwije — Wed, 21 Feb 2018 15:25:27 GMT

Hi Friends

Pls help me to solve this issue

Re: how to match date between period

upaliwije — Wed, 21 Feb 2018 15:36:28 GMT

I have Attached my QV doc so that you may help me easily

Re: how to match date between period

isingh30 — Wed, 21 Feb 2018 15:54:07 GMT

Remove Concatenate & then try. Let me know.

Thanks.

Re: how to match date between period

upaliwije — Wed, 21 Feb 2018 15:57:55 GMT

Dear Singh

Then all records from both table where policy No is matched is loaded without considering the period

Re: how to match date between period

isingh30 — Wed, 21 Feb 2018 16:03:15 GMT

Can we do this in UI? Or you want to do this in the main script only..

Thanks.

Re: how to match date between period

upaliwije — Wed, 21 Feb 2018 16:05:34 GMT

Yes main script only

Re: how to match date between period

upaliwije — Thu, 22 Feb 2018 03:00:09 GMT

Can some one suggest an alternative way to accomplish my requirment please

Re: how to match date between period

shilpan — Thu, 22 Feb 2018 04:16:14 GMT

How about extended interval match?

Re: how to match date between period

shilpan — Thu, 22 Feb 2018 04:18:04 GMT

See my article on extended IntervalMatch

Qlikview Tutorial « Learn Qlikview

Re: how to match date between period

upaliwije — Thu, 22 Feb 2018 04:42:49 GMT

Can you please modify my script so that I can understand better. Thanks

Re: how to match date between period

bnichol — Thu, 22 Feb 2018 16:20:26 GMT

I've attached a modified version of your code.