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.
Solved! Go to Solution.
I am pretty sure this is the result you're looking for.
I have attached the qvw. Take a look.
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?
I suggest you read how the Round function works:
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;