Matching Disunified Data

[m_white]

I have 2 sheets that I am trying to use to obtain the scores of that particular device but the data is not unified automatically because the values differ slightly. How do I go about creating a formula that will match the "buzz values" to the cell I am trying to pull data from? Below is an example of what I am dealing with: I want the data value on the right to automatically associate the score from the data on the left by matching key words/value. 

[JonnyPoole]

Is the pattern just to find the complete 'right' string "001BR_010" within the 'left' string "001BR_010-FN2" ? If yes, is it always the case where the match is such that the 'left' string' starts with the 'right string' ?  The more specific and restrictive the matching is, the more possible it would be in Qlik in my opinion.  

I can see ways of doing this in the script to link two tables or just in the UI with Set Analysis, but, only to a point. 

[pravinboniface]

@m_white I would create a link table that has 2 columns, BuzzKey and BuzzValue with 2 entries as follows:
1,001BR_010-FN2

1,001BR_010

This link table will help you join the two tables via BuzzKey.

To build the link table entries, you can use some of the string matching techniques proposed here:

https://community.qlik.com/t5/QlikView-App-Dev/String-Comparison/td-p/615583

Hope that helps!

[m_white]

Yes, I am trying to find the right string within the left string. I am trying to do this because I need to pull that device's score but our data splits the device list string up so the data is not making the connection. The string on the right is within the same sheet as the score; the left string is a separate sheet. So if I can find a way for the string on the left to recognize the right, it will automatically pull the necessary score.

[rwunderlich]

I"m assuming the tables that hold the right and left strings have other fields as well, but in this example I'll just those fields as samples in previously loaded tables. 

SET vDelim = chr(01);

Lefts:
LOAD * Inline [
LeftValue
F99
=F1-100-F2
ABC_DEF_HI_JKL
Zork_99
]
;
Rights:
LOAD * Inline [
RightValue
F1-100
DEF_HI
F99
]
;
RightMap:
Mapping Load
  RightValue,
  '$(vDelim)' & RightValue & '$(vDelim)'
Resident Rights
;

// Add the RightValue field to the Lefts table to create a link between LeftValue/RightValue
Left Join (Lefts)
LOAD
  LeftValue,
  TextBetween(MapSubString('RightMap', LeftValue), '$(vDelim)', '$(vDelim)') as RightValue
Resident Lefts
;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com