Hi,
I was working on a project with so many different types of classifications so I have written a generic sub which adds a column to the table with the required classification using IntervalMatch as follows:
sub Interval (Connection, File, Sheet, Start, End, Category, Table, Field)
// Connection -->Name of the connection to the Excel file
// File -->Name of the excel file
// Sheet -->Sheet name in the excel file
// Start -->Start of the Interval Column name in excel file
// End -->End of the Interval Column name in excel file
// Category -->Category or group column name in excel file
// Table -->Name of the table to be classified
// Field -->Name of the column in the table to be classified
$(Field)IntervalTable:
LOAD
*,
AutoNumber(Start &'|'& End) AS $(Field)IntervalID;
LOAD
$(Category) as Category,
$(Start) as Start,
$(End) as End
FROM [lib://$(Connection)/$(File).xls]
(biff, embedded labels, table is $(Sheet));
IntervalLink:
INTERVALMATCH($(Field))
LOAD
Start,
End
RESIDENT $(Field)IntervalTable;
LEFT JOIN ($(Table)) LOAD DISTINCT
$(Field),
AutoNumber(Start &'|'& End) AS $(Field)IntervalID
RESIDENT IntervalLink;
drop table IntervalLink;
end sub
A sample call would be:
call Interval ('Data', 'IntervalMatch', '[AgeGroup]', '[AgeFrom]', '[AgeTo]', '[RecommendedCourse]', '[Customer]', 'Age');
This will add a column - "RecommendedCourse" based on the age to the Customer table.
This has been helpful on many occasions except in cases where there was a need for the extended syntax of IntervalMatch.
Hope this will help you too but If anyone has a better approach I would be very happy to know.
Best
Ganesh
