Suggest an Idea

[GaneshLakshman]

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

[John_Teichman]

Thank you for your post. I am closing this item; it is a discussion, not an idea.