Closest Number

[samuel_brierley]

Hi all,

just wondering what the most efficient method of finding the closest number between two dimensions is?

i have two tables;

Table 1

[Address]

A_car

Table 2

[Address]

K_car

both A_car and K_car are integers and i want to know what the closest K_car is to each A_car.

thanks

[sunny_talwar]

I guess one way would be to join the two tables and then find the absolute difference between A_car and K_car and keep the one which is the smallest.

TempTable:

LOAD Address,

          A_car

Resident Table1;

Join(NewTable)

LOAD Address,

          K_car

Resident Table2;

FinalTable:

LOAD Address,

          A_car,

          K_car,

          fabs(A_car - K_car) as Diff

Resident TempTable;

Right Join (FinalTable)

LOAD Address,

          Min(Diff) as Diff

Resident FinalTable

Group By Address;

DROP Table TempTable;

[samuel_brierley]

apologies Sunny there lies the problem. although the Address allows for a link between the two tables there is no values that are the same. I've played around with some cartesian tables to no avail.

im going to try and sort this with an expression.

i am going to close this thread.

thanks

[johnw]

Perhaps do loads on table 1 sorted in one order then the other to get the halfway point between each A_car as a min and max on each record, then intervalmatch with the K_cars.