topic Re: How to assing an item to a cluster in QlikView

How to assing an item to a cluster

Anonymous — Wed, 12 Aug 2015 16:18:04 GMT

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!

Re: How to assing an item to a cluster

maximiliano_vel — Wed, 12 Aug 2015 17:06:22 GMT

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;

Re: How to assing an item to a cluster

prabhu0505 — Wed, 12 Aug 2015 17:23:55 GMT

IntervalMatch

Re: How to assing an item to a cluster

Anonymous — Wed, 12 Aug 2015 17:57:29 GMT

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?

Re: How to assing an item to a cluster

maximiliano_vel — Wed, 12 Aug 2015 18:13:12 GMT

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;

Re: How to assing an item to a cluster

Anonymous — Thu, 13 Aug 2015 07:44:10 GMT

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