Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
upaliwije
Contributor II

Period Between

Hi

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

6 Replies
anbu1984
Honored Contributor III

Re: Period Between

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;

upaliwije
Contributor II

Re: Period Between

Thanks

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

anbu1984
Honored Contributor III

Re: Period Between

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;

upaliwije
Contributor II

Re: Period Between

Thanks

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

anbu1984
Honored Contributor III

Re: Period Between

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

Create marching table:

Intervalmatch(loss date, [policy no])

Load from_date, to_date, [policy no]

Resident table2;

Community Browser