//Dynamic IntervalMatch mapping
//Alexander Karlsson - Ze Demoz Teamz - 2013-09-24
//Point to your excel file
temp:
LOAD DISTINCT FieldName
FROM
RiskData.xlsx
(ooxml, embedded labels, table is [Bucket Templates]);
LET noRows = NoOfRows('temp');
for j=0 to $(noRows)-1
let vFieldValue = peek('FieldName',$(j),'temp');
let vFieldEncoded = '[$(vFieldValue)]';
$(vFieldEncoded):
LOAD FieldName,
StartRange,
EndRange,
Label as [$(vFieldValue) Bucket],
SortOrder as [$(vFieldValue) BucketSort]
FROM
RiskData.xlsx
(ooxml, embedded labels, table is [Bucket Templates]) where FieldName = '$(vFieldValue)';
IntervalMatch($(vFieldEncoded))
Left Join ($(vFieldEncoded)) Load StartRange,EndRange Resident $(vFieldEncoded) where FieldName = '$(vFieldValue)';
Next
DROP TABLE temp;
Drop Fields StartRange,EndRange,FieldName;
