topic Re: Period Between in QlikView

Period Between

upaliwije — Sun, 14 Sep 2014 11:24:58 GMT

I have following data in one table

Table 1

Policy No

Premium

Product

From_date

To_date

In table 2 I have following data

Table 2

Policy No

Claim No

Loss date

Claim Paid

I want to load full data from table 1 and concatenate data from table 2 if Loss Date is between From_date and To_Date of table1

Pls help me to write the script for this with this condition

Re: Period Between

anbu1984 — Sun, 14 Sep 2014 12:01:49 GMT

Table:

Load Policy_No,Premium,Product,From_date,To_date From Table1;

Join(Table1)

Load Policy_No,Claim_No,Loss_date,Claim_Paid From Table2;

NoConcatenate

Final:

Load * Resident Table where Loss_date >= From_date And Loss_date <= To_date;

Re: Period Between

upaliwije — Sun, 14 Sep 2014 12:07:36 GMT

Thanks

I think Join will not give the desired result as as in table one same policy no exists more than one time

Re: Period Between

anbu1984 — Sun, 14 Sep 2014 12:24:19 GMT

Check this example

Table:

Load Policy_No,Premium,Product,Date#(From_date,'M/D/YYYY') As From_date,Date#(To_date,'M/D/YYYY') As To_date Inline [

Policy_No,Premium,Product,From_date,To_date

1,1,a,1/1/2014,2/1/2014

1,1,a,2/2/2014,3/1/2014

];

Join(Table)

Load Policy_No,Claim_No,Date#(Loss_date,'M/D/YYYY') As Loss_date,Claim_Paid Inline [

Policy_No,Claim_No,Loss_date,Claim_Paid

1,1,1/2/2014,1

];

NoConcatenate

Final:

Load Month(Loss_date),Month(From_date),* Resident Table where Loss_date >= From_date And Loss_date <= To_date;

Drop table Table;

Re: Period Between

upaliwije — Sun, 14 Sep 2014 12:39:34 GMT

Thanks

But I want all records from table 1 and matching records from table 2 to be loaded

Re: Period Between

anbu1984 — Sun, 14 Sep 2014 12:49:47 GMT

Table:

Load Policy_No,Premium,Product,Date#(From_date,'M/D/YYYY') As From_date,Date#(To_date,'M/D/YYYY') As To_date Inline [

Policy_No,Premium,Product,From_date,To_date

1,1,a,1/1/2014,2/1/2014

1,1,a,2/2/2014,3/1/2014

2,1,a,2/2/2014,3/1/2014

];

Join(Table)

Load Policy_No,Claim_No,Date#(Loss_date,'M/D/YYYY') As Loss_date,Claim_Paid Inline [

Policy_No,Claim_No,Loss_date,Claim_Paid

1,1,1/2/2014,1

];

NoConcatenate

Final:

Load Policy_No,Premium,Product,From_date,To_date,If(Loss_date >= From_date And Loss_date <= To_date,Claim_Paid) Resident Table;

Drop table Table;

Re: Period Between

MarcoWedel — Mon, 15 Sep 2014 05:37:47 GMT

Create marching table:

Intervalmatch(loss date, [policy no])

Load from_date, to_date, [policy no]

Resident table2;