topic Re: To get the result after comparing two parameters (numbers) which are not equal (nearest value) in QlikView

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

mithunjacob — Sat, 16 Nov 2024 21:32:58 GMT

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.

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

Gysbert_Wassenaar — Wed, 23 Jan 2019 17:10:05 GMT

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?

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

jpenuliar — Wed, 23 Jan 2019 17:17:34 GMT

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

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

mithunjacob — Wed, 23 Jan 2019 17:21:31 GMT

Yes, ABC 11:18 should return 0.50

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

jpenuliar — Wed, 23 Jan 2019 17:24:30 GMT

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]);

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

mithunjacob — Wed, 23 Jan 2019 17:25:42 GMT

It should bring out the nearest value.

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

MindaugasBacius — Wed, 23 Jan 2019 19:16:48 GMT

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

I have attached the qvw. Take a look.

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

MindaugasBacius — Wed, 23 Jan 2019 19:23:12 GMT

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

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

mithunjacob — Wed, 23 Jan 2019 19:49:54 GMT

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?

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

MindaugasBacius — Wed, 23 Jan 2019 20:08:29 GMT

I suggest you read how the Round function works:

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

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;