Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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.