3 Replies Latest reply: Dec 2, 2013 10:06 AM by Steve Zagzebski RSS

    Interval Match problem (QVW attached)

    Steve Zagzebski

      I have two problems with the example Interval Match (attached):

       

      1. How do I add the additional requirements of needing to join:

      a. Entity to the Entity_Fact fields

      b Product and the Product_Fact

       

      Ideally the table would look like this since only three records...as the entitys would have to match, the products would have to match and the service date would have to fall between the Effective and Expiration dates

       

      Product_FactProductEntity_FactEntityEffective_YearMthExpiration_YearMthServiceDateOwnership_Pct
      SMDVSMDVDEANDEAN2007012007122007010.1
      SMDVSMDVDEANDEAN2008019999122009010.2
      SNONSNONDEANDEAN2008012013122010010.3

       

      2. Secondly, once that works I just want to add the Ownership_Pct column to the FACT table. And drop all the other tables.

       

      Any help is GREATLY appreciated...

       

      Steve

        • Re: Interval Match problem (QVW attached)
          Jeffrey Vermeire

          Hi Steve,

           

          Take a look at the advanced interval match description in the help file.  It explains how to do the very thing that you're talking about.

           

          To do what you're looking for, try the following:

           

          OWNER_DEF:

          LOAD * INLINE [

              Entity, Product, Effective_YearMth, Expiration_YearMth, Ownership_Pct

              DEAN, SMDV, 200701, 200712,  0.10

              DEAN, SMDV, 200801, 999912,  0.20

              DEAN, SNON, 200801, 201312,  0.30

              DEAN, SNON, 200401, 999912,  0.40

              DNON, SMDV, 200001, 999912,  0.50

              DEAN, DHPP, 200001, 999912,  0.60

          ];

           

          FACT:

          Load * Inline [

          ServiceDate, Entity, Product

          200901, DEAN, SMDV

          200701, DEAN, SMDV

          201001, DEAN, SNON];

           

          Inner Join(OWNER_DEF)

          IntervalMatch([ServiceDate], Entity, Product)

          Load

          Effective_YearMth,

          Expiration_YearMth,

          Entity,

          Product

          Resident

            OWNER_DEF

          ;

           

          Left Join(FACT)

          LOAD

            Entity,

            Product,

            ServiceDate,

            Ownership_Pct

          Resident

            OWNER_DEF

          ;

           

          DROP Table

            OWNER_DEF

          ;

           

          Notice the extended syntax in the interval match.  The first field listed is the "between" value and the last two are other keys to join against.  The fields listed in the load statement have to match the ones in the interval statement.  You can alias them to match, but it's actually easier if they are already named the same.

           

          This actually produces 4 rows since your intervals for {Entity:DEAN, Product:SNON} overlap.

           

          Hope this helps.

           

          Jeff