topic Re: Closest Number in QlikView

Closest Number

samuel_brierley — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Closest Number

sunny_talwar — Fri, 11 Nov 2016 14:29:03 GMT

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;

Re: Closest Number

samuel_brierley — Fri, 11 Nov 2016 16:21:45 GMT

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

Re: Closest Number

johnw — Fri, 11 Nov 2016 20:55:00 GMT

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.