Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

upaliwije
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

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
Not applicable

Re: Period Between

Thanks

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

anbu1984
Not applicable

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;

MarcoWedel
Not applicable

Re: Period Between

Create marching table:

Intervalmatch(loss date, [policy no])

Load from_date, to_date, [policy no]

Resident table2;