7 Replies Latest reply: Jun 9, 2016 5:05 PM by Sunny Talwar RSS

    IntervalMatch extended (more than one keyfield)

    Joe Nadolny

      I am able to use the IntervalMatch prefix using a single keyfield. Now I am attempting to use two keyfields. The on line help leads me to believe I can do this as it shows:

           IntervalMatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement )

       

      further on it states:

       

           "When the IntervalMatch prefix is extended with key fields, it is used to create a table matching discrete numeric values to one or more      numeric intervals, while at the same time matching the values of one or several additional keys."

       

      This leads me to believe I can have more than one keyfield, maybe up to five(5). I have tried this to no avail, (see some code below, no records are returned). I have searched but found no examples. Yes I have examined "IntervalMatch and Slowly Changing Dimensions" by HIC.

       

      Can more than one keyfield be used or not?

           If so how?

       

      If not could the help text be updated?

       

      I am looking at sales data (ship date, part, customer etc) and am looking for the account rep based on a snapshot. Both the part and customer are numeric ID's.

       

      Shipping data loaded here ….

       

      REPDATA:

      Load

      PART

      CUSTOMER

      REP

      START_DATE

      if(isnull(END_DATE) or trim(END_DATE) = '', date(today(), 'MM/DD/YYYY'), END_DATE) as  END_DATE

      FROM SNAPSHOT.QVD

       

      inner join(REPDATA)

       

      IntervalMatch(SHIPDATE, PART, CUSTOMER)

      LOAD START_DATE, END_DATE, PART, CUSTOMER Resident REPDATA;