Solved: Best match connection - Qlik Community

amiroh81 · ‎2017-01-02

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

vinieme12 · ‎2017-01-02

Just use this much for Select in Field

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

PrashantSangle · ‎2017-01-02

hi,

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

amiroh81 · ‎2017-01-02

Of Course,

i have variable = vNum=086123456

and column BO that Contains this data:

123

456

086

08

0

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

Hopes more understandable

Anil_Babu_Samineni · ‎2017-01-02

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)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

vinieme12 · ‎2017-01-02

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

Script

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

Expression

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

amiroh81 · ‎2017-01-02

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

vinieme12 · ‎2017-01-02

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

amiroh81 · ‎2017-01-02

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

vinieme12 · ‎2017-01-02

USe

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

Can you share sample data to try in script?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

amiroh81 · ‎2017-01-02

hi

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 :

0

08

082

083

084

085

086

087

088

089

0830

0831

0832

0833

0834

0835

0836

0838