Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mithunjacob
Partner - Contributor II
Partner - Contributor II

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
jpenuliar
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

9 Replies
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?

talk is cheap, supply exceeds demand
jpenuliar
Partner - Specialist III
Partner - Specialist III

Should ABC;11;18 return 0.5 ? based on the reference table
mithunjacob
Partner - Contributor II
Partner - Contributor II
Author

Yes, ABC 11:18 should return 0.50

jpenuliar
Partner - Specialist III
Partner - Specialist III

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
Partner - Contributor II
Partner - Contributor II
Author

It should bring out the nearest value.
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

Screenshot_1.jpg

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

I have attached the qvw. Take a look.

MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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

mithunjacob
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Specialist III
Partner - Specialist III

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;