Hi David,
One way to do this is to create a new table and populate it with all possible ranges for bins.Then you can link that table to your fact table.
I've modified your load script like this:
--------------------------------------------------------------------
// Add new field that will hold class values
[Sheet1]:
LOAD
x,
y,
Class(y,0.5) as [bins for y *]
FROM [lib://Desktop/Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);
//Get the max value of
TempY:
LOAD
Max(y) as MaxY
Resident Sheet1;
Let vMaxY = Peek('MaxY',0,'TempY');
//Create a temp table with values for interval start, from 0 to max y
IntervalsTemp:
Load
RowNo()/2-0.5 as IntervalStart
autogenerate(Ceil($(vMaxY),1)*2);
//Create a table with all possible intervals that will link to fact table
Intervals:
Load
Class(IntervalStart,0.5) as [bins for y *]
Resident IntervalsTemp;
// Clean up !
Drop Table TempY;
Drop Table IntervalsTemp;
--------------------------------------------------------------------
Of course, there might be a more elegant solution than this.
Alex
