topic Re: IntervalMatch extended (more than one keyfield) in App Development

IntervalMatch extended (more than one keyfield)

Tue, 07 Jun 2016 14:42:14 GMT

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;

Re: IntervalMatch extended (more than one keyfield)

sunny_talwar — Tue, 07 Jun 2016 15:18:25 GMT

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

Re: IntervalMatch extended (more than one keyfield)

Tue, 07 Jun 2016 16:50:32 GMT

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

Re: IntervalMatch extended (more than one keyfield)

sunny_talwar — Tue, 07 Jun 2016 17:11:04 GMT

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;

Re: IntervalMatch extended (more than one keyfield)

Wed, 08 Jun 2016 13:33:05 GMT

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 )

Has anyone got this to work?

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

Re: IntervalMatch extended (more than one keyfield)

sunny_talwar — Wed, 08 Jun 2016 15:20:54 GMT

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;

Re: IntervalMatch extended (more than one keyfield)

Thu, 09 Jun 2016 20:28:54 GMT

Thanks

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

Re: IntervalMatch extended (more than one keyfield)

sunny_talwar — Thu, 09 Jun 2016 21:05:43 GMT

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

Best,

Sunny