topic Identify repeatable dots on the scatter diagram in QlikView

Identify repeatable dots on the scatter diagram

Anonymous — Fri, 15 Apr 2011 14:06:37 GMT

We are creating a scatter diagram with x & y coordinates to identify the surface marks on glass. If the number of dots within the range the 5 pixels is more than 5 then i need to display the alarm mark on the same scatter diagram. How can we plot this kind of graphs using qlikview.

can you please let me know the solution for this problem asap?

many thanks.

Identify repeatable dots on the scatter diagram

vgutkovsky — Fri, 15 Apr 2011 23:05:53 GMT

I don't think this is possible in QV. As far as I know, you can't replace the symbols in a scatter chart. Even if you could somehow manage that, replacing them dynamically would be impossible. However...as of version 10, you can create custom AJAX objects called "QlikView Extensions." In these extensions, you can define a custom chart that will behave however you want. If you need this functionality outside of AJAX, you would need to write a custom OCX object instead of an Extension.

Regards,

Identify repeatable dots on the scatter diagram

johnw — Sat, 16 Apr 2011 00:30:39 GMT

I assume it's not really pixels that you care about, as resizing the chart and so on doesn't seem like it should affect whether or not there's a problem deserving an alarm. I assume the marks would be within a certain distance of each other in X & Y terms.

My goal, I think, might be to handle it with color and possibly size. So all the normal marks might be small and gray, but any group of 5 or more in a specific area would turn bright red. Now the trouble is calculating distance. The distances to check are basically the square of the number of points, so let's hope you don't have a large number of points!

I'd calculate distances in the script rather than on the fly in the chart. Then, for each point, I'd count the other points that are close. I'd set a flag based on that count, or possibly just store the count. If you don't need the distances for anything else, drop them.

OK, this sounds doable. I'm going to try it.

OK, see attached application. I'm not sure it's QUITE what you want, in that you could have five points that would fit inside a circle of diameter 5, but none of the points have four other points in that distance. I'm not even sure how you'd go about testing every possible circle of diameter 5, though. There's probably some advanced mathematics way of doing it in a reasonable time frame, but I'm not a mathematician.

Identify repeatable dots on the scatter diagram

Oleg_Troyansky — Sat, 16 Apr 2011 00:31:16 GMT

If your data is relatively small, you could try to create a solution in your load script. Something along those lines:

1. Create all possible permutations of 2 "dots" - in essence, a "Cartesian multiplication" of the array of all the dots, multiplied by itself (this is why it only works for small volume of data).

2. For each pair of dots, calculate the distance between the dots = Square root of ( (X1-X2) ^ 2 + (Y1-Y2) ^2 )

3. Reload this table, using GROUP BY, counting the number of "secondary dots" per each "primary dot", where the calculated distance is less than 5

4. Store the counts for each dot.

5. In your scatter chart, color all the dots with a neutral color, and use RED color to emphasize those dots with the count > 5

something like this should work if the size of the data allows...

Identify repeatable dots on the scatter diagram

johnw — Sat, 16 Apr 2011 00:47:32 GMT

Apparently Oleg and I are in psychic communication.

Identify repeatable dots on the scatter diagram

Anonymous — Sat, 16 Apr 2011 06:50:09 GMT

Thanks for your quick reply John.

If we had written logic on data loading scrips then it will effect the performance of data load time because we are working on millions of records. This calculation logic will be shown on the scatter diagram after applying certain filters. Based on the picture on the diagram, i want to designate the thickly populated dots based on this logic. Is it possible?

Identify repeatable dots on the scatter diagram

Oleg_Troyansky — Sat, 16 Apr 2011 19:25:35 GMT

John - this was funny.

Bhami - if you know a way to calculate the distances between all possible dots in the chart, then you can use that condition in determining the dot's color. I can't say that I know an easy way of doing it.

Also, your selections must reduce the data down by A LOT, to enable this kind of calculation in real time.

Identify repeatable dots on the scatter diagram

johnw — Mon, 18 Apr 2011 21:25:04 GMT

Easy enough to do the exact same calculation in the chart as in the example. Create a data island to hold a copy of the points and coordinates. Count up the points from the island table in range of the real point. If >=5, color it red and make it bigger. Same basic idea, just in the chart.

QUALIFY *;
[Island]: LOAD * RESIDENT [Points];
UNQUALIFY *;

background color expression: if(count(if(pow(X-Island.X,2)+pow(Y-Island.Y,2)<6.25,Island.ID))>=5,argb(120,255,0,0),argb(120,0,0,0))

z expression: if(count(if(pow(X-Island.X,2)+pow(Y-Island.Y,2)<6.25,Island.ID))>=5,5,1)

Example updated with that.

As Michael says, the more points, the longer this will take to render. You might experiment with performance. If you find that anything over 1000 points causes a serious problem, then set that up as a calculation condition for the chart. Or maybe put a button on the screen that tells them the number of records and estimates the time required to display the chart, and they click it to make the chart display. If they WANT to wait for 5 minutes to see the chart, why stop them? Just make sure they know what they're getting into first. Then probably set it up so that any change in selections removes the chart again. You could handle that by, when they click the button, running a macro that records the current selections. Something like this:

set field = activedocument.variables("vSelections")
field.setcontent activedocument.evaluate("getcurrentselections()"),false

Then for the display condition for your chart, something like this:

getcurrentselections()=vSelections

Seems like it would work. They click the button, it records the current selections, they therefore match the current selections, so it shows the chart. Hit clear, change a selection, anything like that, and the current selections will no longer match the variable, so the chart should go away again.

Identify repeatable dots on the scatter diagram

johnw — Mon, 18 Apr 2011 21:53:44 GMT

I've updated the example with the button approach I mentioned. I've also given a very different solution, using a pivot table to count the number of points within certain ranges. It's not an ideal solution, as you could have a lot of points close together that fall into different classes and thus don't count as a cluster. The main advantage is that it should be very fast. I doubt it would meet your needs, though.

Identify repeatable dots on the scatter diagram

Anonymous — Tue, 19 Apr 2011 06:28:53 GMT

John

Excellent solution. This is 100% solution that i am looking for. Many thanks for your help. It works.

regards,

Bhami

Re: Identify repeatable dots on the scatter diagram

johnw — Fri, 01 Mar 2013 22:30:07 GMT

I was asked in a private message how the script speed could be improved for large sets of points. Say we have 1,000,000 data points. That means we have roughly 500,000,000,000 distances to calculate. Clearly brute force isn't going to work. So how can we do it?

What we need is a way to limit the number of distances we compute. There may be some advanced mathematical way of doing this. As I said, I'm not a mathematician. But here's the first thing that occurred to me:

We want to highlight based on a circle of radius five, or a distance of 2.5. So divide the graph into 2.5x2.5 squares. A point can now only be within 2.5 of another point if the other point is in the same or an adjacent square. Join on adjacent squares instead of a Cartesian join. Assuming we don't actually have that many points that close to each other and we're looking for the exceptions, it should load quickly.

In the attached example, I used 100,000 points. Our earlier approach would have calculated 10,000,000,000 distances, and so simply wouldn't have worked. With the new approach, it took less than half a minute to load. I'm sure the script can be cleaned up, and I didn't thoroughly test. But even if there are errors and inefficiencies, I suspect the basic idea is sound. For real world data sets with clusters being the exception instead of the rule, I think it should perform just fine.

Re: Identify repeatable dots on the scatter diagram

johnw — Mon, 11 Mar 2013 19:16:22 GMT

I was asked to explain a few things about the example - the big autonumber() expression, how to modify it for other data sets, and what I'm doing when I inner join to 1 as the Flag.

First thing to note is that I had a bug, and the expression for my example should have been this:

,autonumber(text(num(class(X,2.5))+pick(iterno(),-2.5,-2.5,-2.5, 0,0, 0,+2.5,+2.5,+2.5)) & ','
& text(num(class(Y,2.5))+pick(iterno(),-2.5, 0,+2.5,-2.5,0,+2.5,-2.5, 0,+2.5))) as Group

Basically, I'd accidentally done squares of 5 on a side instead of 2.5 on a side, which led to comparing more points than necessary. It didn't seem to speed up the 100,000 case, though.

All right. What I'm doing is shown in this picture:

The class() function here is being used to give me the lower left point of the box that the point is in, so for the point in black (28.125,36.875), it gives us the point (27.5,35). All points in that 2.5x2.5 square will be mapped to (27.5,35). The pick(iterno()) part is just a way of iterating through that square AND the eight surrounding squares. So as we go through nine iterations, we will produce the lower left coordinate of the point's square and all surrounding squares. The autonumber just converts these coordinates to a single integer value. What value is unimportant. All that's important is that it will be consistent, always producing the same integer for the same coordinates. There are simpler ways to write this expression. I just wrote the first way that occurred to me.

In this expression, the 2.5 is the radius of the circle that you want to check for points within. If you want to check for points within a different radius, you would plug your desired radius into that expression everywhere you see a 2.5. If you want, you could use a variable like vRadius instead. Your X and Y limits for the data set don't have anything to do with it, only the radius.

After flagging distances within this radius with a 1, I then inner join the table with a 1. Since it's an inner join, all this is doing is throwing out all the distances greater than my radius. There's no reason for us to keep those distances, which would probably just slow down the process of adding up the number of points in the radius.

All that said, I started fiddling around with ways to speed it up, and things ended up looking a little different. It now runs about 3x as fast, loading 100,000 points in about 10 seconds, and a million points in about two and a half minutes. Though I should point out that when I loaded a million points, I expanded the range to 2000x2000 to keep clusters relatively rare. Anyway, the script is still the same idea as before, just better tuned, though it's tuned for version 10 64-bit since that's what I have. There are things that I think should run faster a different way (like not recalculating the base Group nine times), but "fixing" them seemed to only slow it down. Hopefully people can figure out what the new script does based on the explanation of the old script, and then tune it for whatever version they're using.

Here's the new script:

[Points]:
LOAD
recno() as ID
,rand()*1000 as X
,rand()*1000 as Y
AUTOGENERATE 100000
;

[Distances]:
NOCONCATENATE
LOAD *
,200000 * floor(X,2.5)
    + 2 * floor(Y,2.5) as Group
RESIDENT [Points]
;
LEFT JOIN ([Distances])
LOAD
ID as ID2
,X as X2
,Y as Y2
,200000 * floor(X,2.5)
    + 2 * floor(Y,2.5)
    + pick(iterno(),-500005,-500000,-499995,-5,0,5,499995,500000,500005) as Group
RESIDENT [Points]
WHILE iterno() <= 9
;
INNER JOIN ([Distances])
LOAD ID,ID2
RESIDENT [Distances]
WHERE ID <> ID2
AND sqr(X-X2)+sqr(Y-Y2)<=6.25
;
LEFT JOIN ([Points])
LOAD
ID
,count(ID2)>=4 as Flag
RESIDENT [Distances]
GROUP BY ID
;
DROP TABLE [Distances]
;

Re: Identify repeatable dots on the scatter diagram

johnw — Fri, 15 Mar 2013 20:44:59 GMT

Very similar script that lets you plug in a radius as a variable:

LET vRad = .005; // The radius that we're checking for points within
LET vRadSquared = sqr($(vRad)); // to save time calculating later
LET vYM = 1 / $(vRad); // to get an integer from our Y coordinate
// because integers are fast (edit: won't always be an integer)
LET vXM = vYM * 100000; // to separate the X and Y coordinates

[Points]:
LOAD MK,LensPosX as X, LensPosY as Y
FROM Sample.qvd (QVD)

;

[Distances]:
// Group into squares with sides equal to the radius
NOCONCATENATE
LOAD *
, $(vXM) * floor(X,$(vRad))
+ $(vYM) * floor(Y,$(vRad)) as Group
RESIDENT [Points]
;
// Join to same and neighboring groups. All points that are within
// the radius of each other will be in the same or neighboring groups.
LEFT JOIN ([Distances])
LOAD
MK as MK2
,X as X2
,Y as Y2
, $(vXM) * floor(X,$(vRad))
+ $(vYM) * floor(Y,$(vRad))
+ pick(iterno(),-$(vXM)-$(vYM),-$(vXM),-$(vXM)+$(vYM)
,-$(vYM),0,+$(vYM)
,$(vXM)-$(vYM),$(vXM),$(vXM)+$(vYM)) as Group
RESIDENT [Points]
WHILE iterno() <= 9
;
// Keep only distances <= the radius
INNER JOIN ([Distances])
LOAD MK,MK2
RESIDENT [Distances]
WHERE MK <> MK2
AND sqr(X-X2)+sqr(Y-Y2)<= $(vRadSquared)
;

// Join back to our points, counting distances, which
// is to say counting the number of points within the
// radius of this point.
LEFT JOIN ([Points])
LOAD
MK
// Flag the point if there are more than 15 points
// within the radius of it (so 17 or more points in
// a circle with the given radius).
,count(MK2)>15 as Flag
RESIDENT [Distances]
GROUP BY MK
;
DROP TABLE [Distances]
;