To get the result after comparing two parameters (numbers) which are not equal (nearest value)

[mithunjacob]

I have to get extract a column after comparing 2 numeric columns from a reference table.  To clarify, from Table 1, I should pass two column values to the respective columns in the reference table and it should retrieve reference table's  third column. I have attached the data set with expected result. 

[Gysbert_Wassenaar]

Obviously the values don't match those in the reference table. So what's the precise logic that determines if there's a match or not?

[jpenuliar]

Should ABC;11;18 return 0.5 ? based on the reference table

[mithunjacob]

Yes, ABC 11:18 should return 0.50

[jpenuliar · Accepted solution]

Table1:
LOAD
"CLASS",
DRAFT as 'DRAFT (x)',
SPEED as 'SPEED (y)',
Num(Round(DRAFT,0.5),'0.00') as 'DRAFT',
Num(Round(SPEED),'0.00') as 'SPEED'
FROM [lib://AttachedFiles/Data.xlsx]
(ooxml, embedded labels, table is Table);


Left Join(Table1)
LOAD
"CLASS",
DRAFT,
SPEED,
"TRIM"
FROM [lib://AttachedFiles/Data.xlsx]
(ooxml, embedded labels, table is [Reference table]);

[mithunjacob]

It should bring out the nearest value.

[MindaugasBacius]

I am pretty sure this is the result you're looking for.

I have attached the qvw. Take a look.

[MindaugasBacius]

Jpenuliar  's solution is the same as mine by the way

[mithunjacob]

Thank you very much Jpenuliar! Your approach is working good for the sample set I attached. I will accept as solution. Before that one more doubt - what if in Drafts there is 0.25 variance also? I have attached a new sheet here with those sets. Kindly check. If we make Num(Round(DRAFT,0.25),'0.00') as 'DRAFT', one of the record will not come because the data is not there. Its a tricky situation! In case this scenario cannot be covered, will you able to induce/print '0' there from the script?

[MindaugasBacius]

I suggest you read how the Round function works:

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/ChartFunctions/GeneralNumericFun...

But yes, if all the variance is 0.25 then change 0.5 to 0.25.

In case it do not get linked and there are missing values in Reference table then:

tmp:
Directory;
LOAD CLASS, 
     DRAFT, 
     SPEED,
     Round(DRAFT, 0.5) as tmpDRAFT,
     Round(SPEED, 0.5) as tmpSPEED
FROM
[..\Downloads\Data.xlsx]
(ooxml, embedded labels, table is Table);



Directory;
Left Join (tmp)
LOAD CLASS, 
     DRAFT as tmpDRAFT, 
     SPEED as tmpSPEED, 
     TRIM
FROM
[..\Downloads\Data.xlsx]
(ooxml, embedded labels, table is [Reference table]);


tmp2:
LOAD *
	,Alt(TRIM, 0) as newTRIM
Resident tmp;

DROP Table tmp;