topic Re: Best Match solution for numbers? in QlikView

Best Match solution for numbers?

lfalmoguera — Sun, 18 Feb 2018 09:54:16 GMT

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.

Re: Best Match solution for numbers?

swuehl — Sun, 18 Feb 2018 11:00:05 GMT

Maybe like

TableA:

LOAD * INLINE [

NumberA

610

629

628

62974

6510

6298

];

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;

Re: Best Match solution for numbers?

lfalmoguera — Sun, 18 Feb 2018 13:31:59 GMT

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 : )

Re: Best Match solution for numbers?

swuehl — Sun, 18 Feb 2018 19:33:33 GMT

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;