Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thanks
I think Join will not give the desired result as as in table one same policy no exists more than one time
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;
Thanks
But I want all records from table 1 and matching records from table 2 to be loaded
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;
Create marching table:
Intervalmatch(loss date, [policy no])
Load from_date, to_date, [policy no]
Resident table2;