topic Load and Compare @ Load script in QlikView

Load and Compare @ Load script

Fri, 04 Mar 2011 00:40:16 GMT

Hi,

i want to know how to loop in the load script and compare each row value of a column for example:

Id | Field1 | Field2

a | 123 | 098

b | 456 | 765

c | 789 | 432

i want to loop and compare if

a's Field1 = b's Field1, a's Field1 = c's Field1

b's Field1 = a's Field1, b's Field1 = c's Field1

c's Field1 = a's Field1, a's Field1 = b's Field1

i want to do this in the load script..thanks

Load and Compare @ Load script

johnw — Fri, 04 Mar 2011 01:21:51 GMT

So you want to compare fields for EVERY combination of Ids? I'd handle it with a join instead of a loop. Keep in mind that the number of rows in the comparison table will increase drastically with the number of Ids.

Data:
LOAD * INLINE [
Id | Field1 | Field2
a | 123 | 098
b | 456 | 765
c | 789 | 432
d | 123 | 123
e | 135 | 765
] (delimiter is '|');

QUALIFY *;

A: LOAD * RESIDENT [Data];
B: LOAD * RESIDENT [Data];

UNQUALIFY *;

Join: NOCONCATENATE LOAD * RESIDENT A;
OUTER JOIN (Join) LOAD * RESIDENT B;

SET vCompare = if(A.$1=B.$1,'Same','Different') as "$1 Result";

Result:
LOAD
A.Id
,B.Id
,$(vCompare(Field1))
,$(vCompare(Field2))
RESIDENT Join
WHERE A.Id < B.Id
;

DROP TABLES
A
,B
,Join
;

See attached.

Load and Compare @ Load script

johnw — Fri, 04 Mar 2011 01:36:16 GMT

OK, here's a version with a loop through all available fields so that you don't have to list them. This assumes that you want to compare EVERY field in the table, and not just specific fields.

Data:
LOAD * INLINE [
Id | Field1 | Field2
a | 123 | 098
b | 456 | 765
c | 789 | 432
d | 123 | 123
e | 135 | 765
] (delimiter is '|');

QUALIFY *;

A: LOAD * RESIDENT [Data];
B: LOAD * RESIDENT [Data];

UNQUALIFY *;

Result: NOCONCATENATE LOAD * RESIDENT A;
OUTER JOIN (Result) LOAD * RESIDENT B;
INNER JOIN (Result) LOAD A.Id, B.Id RESIDENT Result WHERE A.Id < B.Id;

FOR i=nooffields('Data') TO 1 STEP -1
LET vField=fieldname(i,'Data');
IF '$(vField)' <> 'Id' THEN
LEFT JOIN ([Result])
LOAD A.Id, B.Id, if(A.$(vField)=B.$(vField),'Same','Different') as "$(vField) Result"
RESIDENT [Result];
DROP FIELDS A.$(vField), B.$(vField);
END IF
NEXT

DROP TABLES A, B;

Load and Compare @ Load script

Fri, 04 Mar 2011 02:35:04 GMT

thanks sir John but instead of "Same" and "Different" as a result I want the difference of each fields

example:

A's Field1 - B's Field1, A's Field1- C's Field1

B's Field1 - A's Field1, B's Field1 - C's Field1

C's Field1 - A's Field1, C's Field1 - B's Field1

Load and Compare @ Load script

johnw — Fri, 04 Mar 2011 19:12:45 GMT


mOngkz wrote: thanks sir John but instead of "Same" and "Different" as a result I want the difference of each fields

So... uh... subtract them instead of comparing them? Do you really need me to show you how to subtract two fields? For the first example:

SET vCompare = A.$1 - B.$1 as "$1 Result";

And for the second:

LOAD A.Id, B.Id, A.$(vField) - B.$(vField) as "$(vField) Result"

Also change the "where" in both since you want the difference in both directions.

WHERE A.Id <> B.Id

Load and Compare @ Load script

Sun, 13 Mar 2011 10:39:49 GMT

sir,

heres what I want to do during loading..

1. compare eace ID to every ID in the table.

2. If the ID is not equal to itself it will not compute the distance(latitude and longitude).

3. have a counter that will indicate if there are more than 5 IDs that is less than 5kms from the source ID.

if counter = 5, exit script

here's the picture:

Load and Compare @ Load script

Sun, 13 Mar 2011 14:36:21 GMT

Help me plss..

Load and Compare @ Load script

johnw — Mon, 14 Mar 2011 18:56:19 GMT

Data:
LOAD * INLINE [
Id,Latitude,Longitude
1,14.5988,120.9546
2,14.5974,120.9554
3,14.597,120.9546
4,14.5968,120.9528
5,14.5985,120.9526
];

Temp: NOCONCATENATE LOAD * RESIDENT Data;
OUTER JOIN (Temp) LOAD Id as Id2, Latitude as Latitude2, Longitude as Longitude2 RESIDENT Data;

Distances:
LOAD Id, Id2
,acos(sin(Latitude *pi()/180)*sin(Latitude2*pi()/180)
+cos(Latitude *pi()/180)*cos(Latitude2*pi()/180)
*cos(Longitude2*pi()/180 -Longitude*pi()/180))*6371 as "Distance"
RESIDENT Temp
WHERE Id <> Id2
;

DROP TABLE Temp;

Load and Compare @ Load script

Mon, 14 Mar 2011 23:59:48 GMT

sir,

for question #3, how will i do that?

Load and Compare @ Load script

johnw — Tue, 15 Mar 2011 00:35:26 GMT

For question #3, WHY would you do that? Why would the mere fact that you have 7 locations within 5 km of each other cause your entire script to exit? If you only want to show, say, the five closest locations in a chart, that's one thing. But exiting the script is something completely different.

Load and Compare @ Load script

Tue, 15 Mar 2011 00:50:22 GMT

its just a trigger to tell the person that there is already a cluster..

so instead of loading the entire data(tons of data), if qlikview detected that there is already a cluster..qlikview will pop an alert that there is one location that has points <5kms near him..

Load and Compare @ Load script

johnw — Wed, 16 Mar 2011 01:04:05 GMT

Well, if I understand what you want, I have not thought of any way to do it. The way I'm solving the distance problem requires you to load in the entire table. We could detect after the fact if there is a cluster, but not before we read in the original table and do a cartesian join of itself onto itself.

In a procedural language you could probably do something like this:

SET ROW = 0

DO
ADD 1 TO ROW
SET COUNT = 0
FOR I = 1 TO ROW - 1
CALCULATE DISTANCE
IF DISTANCE < 5
ADD 1 TO COUNT
IF COUNT > 5
EXIT SCRIPT WITH ALARM
END IF
END IF
NEXT I
UNTIL NO MORE ROWS

But I don't see a way to convert a regular load into that sort of procedure.