Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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

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. 

opt.png

Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

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

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]);
9 Replies
MVP & Luminary
MVP & Luminary

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

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?

talk is cheap, supply exceeds demand
Partner
Partner

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

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

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

Yes, ABC 11:18 should return 0.50

Partner
Partner

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

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

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

It should bring out the nearest value.
MindaugasBacius
Valued Contributor III

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

Screenshot_1.jpg

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

I have attached the qvw. Take a look.

MindaugasBacius
Valued Contributor III

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

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

Partner
Partner

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

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
Valued Contributor III

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

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;