How to assing an item to a cluster

[Anonymous]

Hi,

I have a table with a list of customers with a certain quantity.

In another table there are ranges of quantities in order to assign each customer to a cluster (low, medium..., high).

I found a solution (in attach, with data), but I would like to know if there are smarter ways, considering that I have a large amount of data to deal with (I suppose my join is not the best solution).

Thanks!

E

[maximiliano_vel]

Try this

FASCE:

LOAD CUSTOMER,

     QUANTITY,

     KEY

FROM Dati2.xlsx (ooxml, embedded labels, table is Sheet2);

CLUSTER:

LOAD CLUSTER,

     MIN,

     MAX,

     KEY

FROM Dati2.xlsx (ooxml, embedded labels, table is Sheet1);

INNER JOIN (CLUSTER) IntervalMatch (QUANTITY, KEY) LOAD MIN, MAX, KEY RESIDENT CLUSTER;

[prabhu0505]

IntervalMatch

[Anonymous]

Thank you very much! but I need a datamodel with just one table with fields CUSTOMER and CLUSTER (as in my solution)... Is it possible?

E

[maximiliano_vel]

A work around...

FASCE:

LOAD CUSTOMER,

     QUANTITY,

     KEY

FROM Dati2.xlsx (ooxml, embedded labels, table is Sheet2);

CLUSTER:

LOAD CLUSTER,

     MIN,

     MAX,

     KEY

FROM Dati2.xlsx (ooxml, embedded labels, table is Sheet1);

INNER JOIN (CLUSTER) IntervalMatch (QUANTITY, KEY) LOAD Distinct MIN, MAX, KEY RESIDENT CLUSTER;

ClusterMap:

MAPPING

LOAD QUANTITY & KEY,

     CLUSTER

RESIDENT CLUSTER;

Data:

LOAD CUSTOMER,

     ApplyMap('ClusterMap', QUANTITY & KEY, Null()) as CLUSTER

RESIDENT FASCE;

DROP Table FASCE, CLUSTER;

[Anonymous]

Thank you!

The problem is that QUANTITY is a float number with 5 decimal and KEY can have many values... I don't know if performance is better than other solutions.

Someone has other solutions?

Thanks

Elena