IntervalMatch extended (more than one keyfield)

[#1]

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;

[sunny_talwar]

I think you can use more than one key fields. Have you tried running the above script? Gives you any error?

[#3]

Have you tried running the above script? Gives you any error?

From my original post:

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

[sunny_talwar]

Can you try with a concatenated Key?

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,

          AutoNumber(PART&CUSTOMER) as Key

FROM SNAPSHOT.QVD

Inner Join(REPDATA)

IntervalMatch(SHIPDATE, Key)

LOAD START_DATE,

          END_DATE,

          Key

Resident REPDATA;

[#5]

I will attempt to ask the question again.

The way I read the help text it says you can have more than one key field when using the interval match prefix.

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

"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."

Has anyone got this to work?

I am not looking for a composite key, I already know how to do that.

[sunny_talwar]

The best way to check is to test it out. Here is a code which seems to be working for me

Table1:

LOAD Customer,

    Part,

    Start,

    Date(If(IsNull(End) or Trim(End) = '', Today(), End)) as  End

FROM

Community_219851.xlsx

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD Customer,

    Part,

    SHIP_DATE,

    Value

FROM

Community_219851.xlsx

(ooxml, embedded labels, table is Sheet2);

Left Join(Table1)

IntervalMatch(SHIP_DATE, Customer, Part)

LOAD Start,

  End,

  Customer,

    Part

Resident Table1;

Left Join (Table1)

LOAD *

Resident Table2;

DROP Table Table2;

[#7]

Thanks

I found the problem was in the key fields I was creating to link the tables together.

[sunny_talwar]

Awesome, now that you got everything you needed, I would suggest closing this thread by marking the correct and helpful responses.

Best,

Sunny