6 Replies Latest reply: Nov 26, 2013 2:26 PM by Henric Cronström RSS

    Error in Interval Match

    Steve Zagzebski

      Looking for what I am doing wrong with interval match below.

       

      I have two feilds in the   F_MEDICAL_CLAIM_DETAIL load called "Product-Entity" and
      "OwnerClaimID" I was hoping to join to those two fields to the right side of the join.

      Then I have a field "Incurred YearMonth" in the which I need to fall between EFFECTIVE_YEARMTH and EXPIRATION_YEARMTH.

       

      However it doesnt seem to be joining accurately. Any ideas?

       

      F_MEDICAL_CLAIM_DETAIL:

      Load *,
      Resident F_MEDICAL_CLAIM_DETAIL_PRE;

      left Join (F_MEDICAL_CLAIM_DETAIL)
      IntervalMatch([Incurred YearMonth])

      LOAD
      EFFECTIVE_YEARMTH,
      EXPIRATION_YEARMTH,
      [Product-Entity],
      OwnerClaimID,
      OWNERSHIP_PCT,
      OWNER_DEFINITION,
      UPDATE_DATE_D_OWNER_CLAIMS
      FROM$(vInputFile11)
      (qvd);

       

      Thanks,

      Steve

        • Re: Error in Interval Match
          Henric Cronström

          The Load that follows an IntervalMatch prefix should only have two fields: Lower bound and upper bound. And the field in the brackets should be the number that you want to match against the interval. You use F_MEDICAL_CLAIM_DETAIL when you should use [Incurred YearMonth].

           

          See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

           

          HIC

            • Re: Error in Interval Match
              Steve Zagzebski

              Thanks for the response. Your blog was very informative.

               

              However what happens in the case (like mine) where you need to use the interval match to get fit the date into ranges from a table BUT ALSO need to join on an additional field to join on.

               

              Like in the simplied tables below I need to join on Provider ID and I need to see where service date falls between the Begin Date and End Date..

               

              PROVIDER_FACT_TABLE:
              RecordProvider IDService Date
              1John1/1/2007
              2John1/1/2009
              3Steve1/1/2011
              4Steve1/1/2014
              PROVIDER_ALLOCATION TABLE:
              ProviderBegin DateEnd DateAllocation_PCT
              John1/1/200012/31/200750%
              John1/1/20081/1/2013100%
              Steve1/1/200012/31/200720%
              Steve1/1/20081/1/201340%
              RESULT:
              RecordProvider IDService DateAllocation_PCT
              1John1/1/200750%
              2John1/1/2009100%
              3Steve1/1/201120%
              4Steve1/1/201440%

               

              Steve

                • Re: Re: Error in Interval Match
                  Henric Cronström

                  I am not sure I understand why you want to join. I would instead go for the following data model:

                   

                  Image1.png

                   

                  HIC

                  • Re: Re: Error in Interval Match
                    Henric Cronström

                    ... but should you want to join, you can do that too. I think the result is correct.

                     

                    HIC

                      • Re: Error in Interval Match
                        Steve Zagzebski

                        Thanks for the help - I am learning!

                         

                        I guess maybe I didn't totally explain myself. My goals was to just link on the Provider ID AND use interval match to find the proper record that the service date fit into , THEN take the field(s) I need like "Allocation_PCT" and make it part of the PROVIDER_FACT_TABLE.  My intention is not to create or keep any additional tables just to join a new field(s) into the PROVIDER_FACT_TABLE.

                         

                        Does that make sense?

                         

                        Steve

                          • Re: Error in Interval Match
                            Henric Cronström

                            If you use the three-table solution (in the script) I suggested above, you will be able to create exactly the table you want when you create the application UI. That's one way to do do it.

                             

                            Another way is to join all three tables in the script:

                                 Facts: Load ... from Facts ;

                                 Allocation: Load ... from Allocation ;

                                 Left Join (Facts) IntervalMatch (...) Load ... Resident Allocation;

                                 Left Join (Facts) Load ... Resident Allocation;

                                 Drop Table Allocation;

                                 Drop Fields <List of fields you don't want>;

                             

                            But this is somewhat risky: If a date belongs to two intervals, e.g. a date is the end of one interval and the beginning of another, you will get a fact table with too many records. But the three-table solution is safe...

                             

                            HIC