Find closest match

[YPOC]

Hello,

I have two tables that I want to match. The table 'parts' consists of thousands of entries with the columns 'count', 'depth', and 'name'. Count always is an integer, and depth always is a float value, name is just an identifier for that part/row.

Then I have another table 'types' with the columns 'type', 'count', and 'depth'. Now I want to match the rows from table parts with the entries of types. Each part should match its count exactly to the count in types, and match the depth to the closest value in types (Optionally with a margin ε, but let's worry about that later).

I thought about adding a calculated column to the parts table, which will be filled with the type value from types, so the two tables can be easily matched. But I struggle coming up with the expressions for that calculated column. Is this even the right way to go? Could you please help me out?

Example tables:

[types]
LOAD * INLINE [
type,count,depth
Test1,44,7.206
Test2,44,8.123
Test3,17,8.321
](delimiter is ',');

[parts]
LOAD * INLINE [
name,count,depth            // calculatedType (w/ or w/o ε<=0.01)
1124-135,17,8.322           // Test3
1124-136,17,8.319           // Test3
1156-432,17,7.212           // None or Test3
1157-321,44,8.125           // Test2
1184-521,44,7.153           // Test1
1195-243,44,8.233           // None or Test2
](delimiter is ',');

[MayilVahanan · Accepted solution]

Hi @YPOC 

Try with intervalmatch concept like below

[parts]:
LOAD * INLINE [
name,count,depth
1124-135,17,8.322
1124-136,17,8.319
1156-432,17,7.212
1157-321,44,8.125
1184-521,44,7.153
1195-243,44,8.233
](delimiter is ',');

[types]:
LOAD depth+0.1 as depthToRange, depth-0.1 as depthFromRange,type,count, depth as d1 INLINE [
type,count,depth
Test1,44,7.206
Test2,44,8.123
Test3,17,8.321
](delimiter is ',');

Inner Join
IntervalMatch(depth, count)
LOAD depthFromRange, depthToRange,count Resident types;

Join
LOAD * Resident parts;

DROP Table parts;

Note: you can change the from & to range based on ur requirement. Rename the depth column in table Types. Otherwise, result will not be correct.

Final output:

[YPOC]

Hello Mayil, thank you very much for helping me. I'm trying to apply your solution to my data, but I get an error which seems super simple but I just can't figure it out.

In the Data Editor I've put

Inner Join
IntervalMatch([features/Tauchtiefe], [features/Messerzahl])
LOAD TauchtiefeUnteresLimit, TauchtiefeOberesLimit, Messerzahl Resident Radtypen;

Join
LOAD * Resident features;

DROP Table features;

but I get the error Field 'Messerzahl' not found, happening in the first statement. I'm sure my table 'Radtypen' contains the field 'Messerzahl', as you can see in the pictures I've attached. What am I doing wrong? It finds the fields TauchtiefeUnteresLimit and TauchtiefeOberesLimit, but not the third one.

Edit: I just noticed that when I put IntervalMatch([Tauchtiefe], [Messerzahl]) instead of those from the features tables there will be no error and the field 'Messerzahl' will be found, however the resulting data is obviously incorrect. This confuses me even more.

Edit2: Ok, I think I understand now WHAT I'm doing wrong, but not WHY it is wrong. IntervalMatch expects as first parameter a field that will be matched between two intervals, and following parameters will be matched exactly to the following table that gets resident loaded. However the corresponding fields must have the same name(?). So when I write ...[Messerzahl] as [features/Messerzahl] Resident Radtypen; then it will work. That is very counter intuitive. Or maybe my thinking is wrong.

[MayilVahanan]

Hi @YPOC 

FYIP, In intervalmatch concept, comparing field need to be same.

For ex: IntervalMatch([features/Tauchtiefe], [features/Messerzahl])

Can you rename the "[features/Messerzahl]" as Messerzahl in ur data model. Otherwise, qlik can't recognize to compare the values of unknown field and result may be unexpected.

edit: 

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

please refer the help link to understand more about concept