Best Match solution for numbers?

[lfalmoguera]

Hi all,

I  am trying to find a solution two compara two rows of numbers and get the best match result.

uick example:

tableA		tableB			Result Table
numberA		numberMatch	Result		numberA	Result
610		6	A		610	A
629		62	B		629	B
628		628	A		628	A
62974		6297	C		62974	C
6510		651	A		6510	A
6298		63	B		63	B
64		64	C		64	C
65					65	A
66					66	A
67					67	A
68					68	A
69					69	A

Any quick to achieve it?

I am trying with mathc(), wildmatch() and several loops, but I am struggling.

Thanks a lot in advance.

Regards.

[swuehl]

Maybe like

TableA:

LOAD * INLINE [

NumberA

610

629

628

62974

6510

6298

64

65

66

67

68

69

];

TableB:

LOAD *, Len(NumberMatch) as LenMatch INLINE [

NumberMatch, Result

6,A

62,B

628,A

6297,C

651,A

63,B

64,C

];

MAP:

MAPPING

LOAD '~'&NumberMatch, '<'&Result&'>'

Resident TableB

ORDER BY LenMatch desc;

Result:

LOAD NumberA,

          Textbetween(Mapsubstring('MAP','~'&NumberA),'<','>') as Result

Resident TableA;

[lfalmoguera]

Wow!

It worked perfectly! I don´t know how scalable it would be as I need to cross quite a million records, but it worked so fine!

Thanks a lot!

PD. The worst part is that I do not undersand the code and how it works : )

[swuehl]

Remove the Textbetween from the last load to see the mapping, this should make it easier to understand.

(I assume you know how mapping works in Qlik, if not, start with MAPPING LOAD and MapSubString() function in the HELP):

Result:

LOAD NumberA,

          Mapsubstring('MAP','~'&NumberA) as Result

Resident TableA;