6 Replies Latest reply: Sep 15, 2014 1:37 AM by Marco Wedel RSS

    Period Between

    UPALI WIJERATNE

      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

        • Re: Period Between
          anbu cheliyan

          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
              UPALI WIJERATNE

              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
                  anbu cheliyan

                  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
                      UPALI WIJERATNE

                      Thanks

                       

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

                        • Re: Period Between
                          anbu cheliyan

                          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
                    Marco Wedel

                    Create marching table:

                     

                    Intervalmatch(loss date, [policy no])

                    Load from_date, to_date, [policy no]

                    Resident table2;