11 Replies Latest reply: Jan 9, 2014 5:01 PM by Steve Zagzebski RSS

    Interval Match Problem

    Steve Zagzebski

      I have tried looking at all the discussions on this site but still can't get what I want! I keep getting results different that what I exopect and with a sythnetic table. Seems basic all I want is ONE table with the following results...

       

       

      EntityServiceDateOwnership_PctEffective_YearMthExpiration_YearMth
      DEAN2009010.2200801999912
      DEAN2007010.1200701200712
      DNON2010010.6200901999912

       

      LOAD * INLINE [
      Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
      DEAN, 200701, 200712,  0.10
      DEAN, 200801, 999912,  0.20
      DNON, 200001, 200812,  0.50
      DNON, 200901, 999912,  0.60
      ]
      ;

      FACT:
      Load * Inline [
      Entity, ServiceDate
      DEAN, 200901
      DEAN, 200701
      DNON, 201001]
      ;

      Left Join(FACT)
      IntervalMatch([ServiceDate],Entity)

      Load
      Effective_YearMth,
      Expiration_YearMth,
      Entity
      Resident OWNER_DEF;

       

        • Re: Interval Match Problem
          Srikanth P

          I believe, In your requirement, IntervelMatch won't fix the problem because both tables having Entity.

           

          Please try code like below:

           

          TEMP:

          LOAD * INLINE [

          Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

          DEAN, 200701, 200712,  0.10

          DEAN, 200801, 999912,  0.20

          DNON, 200001, 200812,  0.50

          DNON, 200901, 999912,  0.60

          ];

           

          Left Join

          Load * Inline [

          Entity, ServiceDate

          DEAN, 200901

          DEAN, 200701

          DNON, 201001];

           

          FINAL:

          LOAD * Where FLAG;

          LOAD * , IF(ServiceDate >= Effective_YearMth AND ServiceDate <= Expiration_YearMth , 1,0) AS FLAG

          Resident TEMP;

           

          DROP Table TEMP;

           

          DROP Field FLAG ;

          • Re: Interval Match Problem
            Henric Cronström

            I would not join the tables - but you can if you know that there is a one-to-one relationship between the dates and the intervals. I would instead go for the following model:

            Data model.png

            HIC

              • Re: Interval Match Problem
                Steve Zagzebski


                Henric -

                 

                Thanks for the response. Is there any way to just get all the data into the fact table. and not have to deal with a bridge table. we have quite a large model and I am hoping just to bring all the data into the FACT table.

                 

                Steve

                  • Re: Re: Interval Match Problem
                    Henric Cronström

                    No problem. Check attached script.

                     

                    HIC

                      • Re: Re: Interval Match Problem
                        Steve Zagzebski

                        Thanks again.

                         

                        Getting close. However the resulting table is the Owner Def table. I would lose my FACT table - which is the main table in our model. The Owner Def table is just an attributes table that can/should go away after using it in the interval match..

                         

                        Any thoughts?

                         

                        Steve

                          • Re: Interval Match Problem
                            Srikanth P

                            Just change the Join to FACT table like below:

                             

                            OWNER_DEF:

                            LOAD * INLINE [

                            Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct

                            DEAN, 200701, 200712,  0.10

                            DEAN, 200801, 999912,  0.20

                            DNON, 200001, 200812,  0.50

                            DNON, 200901, 999912,  0.60

                            ];

                             

                             

                            FACT:

                            Load * Inline [

                            Entity, ServiceDate

                            DEAN, 200901

                            DEAN, 200701

                            DNON, 201001];

                             

                            Join (FACT)

                            IntervalMatch([ServiceDate],Entity)

                            Load

                            Effective_YearMth,

                            Expiration_YearMth,

                            Entity

                            Resident OWNER_DEF;

                             

                            Left Join (FACT) // Left Join Keep only all records in FACT Tabel

                            Load * Resident OWNER_DEF;

                             

                            Drop Table OWNER_DEF;

                            • Re: Interval Match Problem
                              Henric Cronström

                              No, the table is the join between the tables, so it contains all information from both tables. Yes, it is labeled OWNER_DEF, but this is just a matter of labeling. To change the join order - so that you start with the joining to the FACT table - does not change anything.

                               

                              HIC

                                • Re: Interval Match Problem
                                  Steve Zagzebski

                                  Okay I think I have it finally! My last question is that an extra row is created in the resulting table (bolded record with DNON and no service date or area). This record was not from the FACT table  but got created through the join process. Any way to only have the FACT table records records?

                                   

                                  The resulting table is (updated the code below slighlty). :

                                   

                                  EntityServiceDateOwnership_PctEffective_YearMthExpiration_YearMthArea
                                  DEAN2009010.2200801999912North
                                  DEAN2007010.1200701200712South
                                  DNON2010010.6200901999912East
                                  DNON 0.5200001200812
                                  TEST201001 West

                                   

                                   

                                  LOAD * INLINE [
                                  Entity, Effective_YearMth, Expiration_YearMth, Ownership_Pct
                                  DEAN, 200701, 200712,  0.10
                                  DEAN, 200801, 999912,  0.20
                                  DNON, 200001, 200812,  0.50
                                  DNON, 200901, 999912,  0.60
                                  ]
                                  ;

                                  FACT:
                                  Load * Inline [
                                  Entity, ServiceDate, Area
                                  DEAN, 200901, North
                                  DEAN, 200701, South
                                  DNON, 201001, East
                                  TEST, 201001, West]
                                  ;

                                  Join (OWNER_DEF)
                                  IntervalMatch([ServiceDate],Entity)

                                  Load
                                  Effective_YearMth,
                                  Expiration_YearMth,
                                  Entity
                                  Resident OWNER_DEF;

                                  Join (OWNER_DEF)
                                  Load * Resident FACT;

                                  Drop Table FACT;