Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
I think you can use more than one key fields. Have you tried running the above script? Gives you any error?
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
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;
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.
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;
Thanks
I found the problem was in the key fields I was creating to link the tables together.
Awesome, now that you got everything you needed, I would suggest closing this thread by marking the correct and helpful responses.
Best,
Sunny