Load and Compare @ Load script

[#1]

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

[johnw]

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.

[johnw]

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;

[#4]

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

[johnw]

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

[#6]

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:

[#7]

Help me plss..

[johnw]

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;

[#9]

sir,

for question #3, how will i do that?

[johnw]

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.