topic Re: Best match connection in QlikView

Best match connection

amiroh81 — Wed, 25 Nov 2020 16:16:04 GMT

I have a 10-digit field and another field that contains a different number of digits between 1-10.

I would like to connect the fields when the connection will be based on the most accurate.

I thought about something like this:

=if(BO=vNum,BO,

if(BO=mid(vNum,1,10),BO,

if(BO=mid(vNum,1,9),BO,

if(BO=mid(vNum,1,8),BO,

if(BO=mid(vNum,1,7),BO,

if(BO=mid(vNum,1,6),BO,

if(BO=mid(vNum,1,5),BO,

if(BO=mid(vNum,1,4),BO,

if(BO=mid(vNum,1,3),BO,

if(BO=mid(vNum,1,2),BO,

if(BO=mid(vNum,1,1),BO,)))))))))))

vNum=086123456

BO=086

BO=08

BO=0

What happens I get the exact result (For example 4 digits but I get even less fit)

How to find the best match only?

Thanks in advance

Re: Best match connection

PrashantSangle — Mon, 02 Jan 2017 12:24:46 GMT

hi,

can you explain little bit more with sample with data and desired output

Regards

Re: Best match connection

amiroh81 — Mon, 02 Jan 2017 12:31:34 GMT

Of Course,

i have variable = vNum=086123456

and column BO that Contains this data:

123

456

086

i want to connection between the BO column and vNum When the beginning is important

so in this case is :

086

but i want only the most exact result in this case:

086

Hopes more understandable

Re: Best match connection

Anil_Babu_Samineni — Mon, 02 Jan 2017 12:36:35 GMT

Requirement still not clear,

As i understand you have BO Field and you want to merge them into variable like vNum. I hope this is not correct information

i want to connection between the BO column and vNum When the beginning is important

so in this case is :

086

08

0

but i want only the most exact result in this case:

086

This? you mean Max(FieldName)

Re: Best match connection

vinieme12 — Mon, 02 Jan 2017 13:31:13 GMT

Try this=aggr(WildMatch(vNum,BO&'*'),BO)

Script

load * inline [
BO
123
456
086
08
0
];

Expression

Re: Best match connection

amiroh81 — Mon, 02 Jan 2017 13:50:37 GMT

Hi,

this is help to get all value are start with the same pattern

i want to get the only the most exact result in this case:

086

Re: Best match connection

vinieme12 — Mon, 02 Jan 2017 14:28:00 GMT

do you want to do this in Script or straight table?

Re: Best match connection

amiroh81 — Mon, 02 Jan 2017 14:30:19 GMT

both of them but the most impotent to me is to insert to variable or to select in field

Re: Best match connection

vinieme12 — Mon, 02 Jan 2017 15:04:33 GMT

USe

=if(BO = Maxstring(TOTAL if(Wildmatch(086123456,BO&'*'),BO)),BO)

Can you share sample data to try in script?

Re: Best match connection

amiroh81 — Mon, 02 Jan 2017 18:31:49 GMT

this is almost what i looking for.

if i take this if(BO = Maxstring(TOTAL if(Wildmatch(086123456,BO&'*'),BO)),BO) on Text object its give me the right value (086)

but if i try to to insert this command into button object and to do select in field its not filter me the right value.

for example this is partial of the value i have on BO field and if the user insert the number 086123456 to vNum i want that BO filed will be filter for 086 value only :

082

083

084

085

086

087

088

089

0830

0831

0832

0833

0834

0835

0836

0838

Re: Best match connection

vinieme12 — Mon, 02 Jan 2017 18:46:31 GMT

Just use this much for Select in Field

=Maxstring(TOTAL if(Wildmatch(086123456,BO&'*'),BO))

Re: Best match connection

amiroh81 — Mon, 02 Jan 2017 18:59:40 GMT

right its work.

thanks