4 Replies Latest reply: Jun 27, 2012 8:51 AM by Stefan Wühl RSS

    Looking for partial matched data

      Hello,

       

      I've got two tables both contain a product Id but in one table the product Id maybe within a description field and/or may contain extra spaces.

       

      I would like to load the two tables and show where there is a partial match from table 1 of a specific column ie product id in all the columns in table 2.

       

      Is this possible ?

       

      There is no actual input of the product Ids as this would go into 1,000 rows.

       

      Ive looked at mixmatch but this doesnt seem to suit.

       

      does anyone have any examples of this?

        • Re: Looking for partial matched data
          sivasankar kanagasabai

          Probably the following link will help. http://community.qlik.com/message/131099#131099 Regards Siva Sankar

          • Re: Looking for partial matched data
            Stefan Wühl

            Would be nice to see some sample lines of data together with your expected outcome to better understand your requirements and setting.

             

            >There is no actual input of the product Ids as this would go into 1,000 rows.

             

            Sorry, I haven't understood this.

             

            If you can tell any rules to read the product Ids from the description field, I would go for creating a new product ID field in table 2 by parsing the description field.

            • Re: Looking for partial matched data

              TABLE 1

               

              ID, DESCRIPTION, PRICE

               

              ABC123, Widget ALM, 101

              ABD122, Wakka AKK, 80

              ABC122, Widget ALK, 90

               

              TABLE 2

               

              ID, DESCRIPTION, PRICE

               

              ABC0123, ALM Top, 90

              03ndd, ABD122Wakka, 200

              Colin, ABC0122Wid ALK, 30

               

               

              Two tables of data. I would like to use Fuzzy logic basically using the desciption field to find similarity between the two tables as ID field is inconsistant in one table.

               

              Is it possible to return matches between the two to produce an output like :

               

              ID, DESCRIPTION, PRICE, ID2, DESCRIPTION2, PRICE2,

               

              ABC123, Widget ALM, 101, ABC0123, ALM Top, 90

              ABD122, Wakka AKK, 80, 03ndd, ABD122Wakka, 200

              ABC122, Widget ALK, 90, Colin, ABC0122Wid ALK, 30

                • Re: Looking for partial matched data
                  Stefan Wühl

                  I noticed that you created another thread, just for the records, here also the solution I just posted:

                   

                  "A real fuzzy logic might be hard to implement in QV (of course you can check out e.g. VBScript macros that you can make use of). If a wildmatch comparison of the single Description words is enough (i.e. for ID ABC123, try matching  '*Widget*' or '*ALM*' with the DESCRIPTION2 field values), you can do it like this:

                   

                  SET Del = '@!+';

                   

                  TABLE1:

                  LOAD *, subfield(DESCRIPTION,' ') as DESCSUB INLINE [

                  ID, DESCRIPTION, PRICE

                  ABC123, Widget ALM, 101

                  ABD122, Wakka AKK, 80

                  ABC122, Widget ALK, 90

                  ];

                   

                  MAP1:

                  MAPPING

                  LOAD DESCSUB as Map1, '$(Del)' & ID &'$(Del)' Resident TABLE1;

                   

                  TABLE2:

                  Left Join LOAD *, Textbetween(MapSubString('MAP1', DESCRIPTION2),'$(Del)','$(Del)') as ID INLINE [

                  ID2, DESCRIPTION2, PRICE2

                  ABC0123, ALM Top, 90

                  03ndd, ABD122Wakka, 200

                  Colin, ABC0122Wid ALK, 30

                  ];

                   

                  drop field DESCSUB;

                   

                  The defined Del variable should be a sequence of characters that are not expected to appear in your DESCRIPTION fields."

                   

                  Hope this helps,

                  Stefan