1 Reply Latest reply: Oct 3, 2018 4:04 PM by vamsee duggirala RSS

    Using a list of values to create a flag using match and wild match

    James Eckstein

      Hi All and thanks in advance for any help!

       

      I have a table called data. This table has two fields. ID and CODE. I want to create a third field called Matched_Flag.

       

      I also have two other tables. The first is Exact_Match which has one field called exact_match.  The other table is called Wild_Match which has 1 field called wild_match.


      Data:

      IDCodeMatched_Flag
      1A1
      2A2
      3A3
      4A4
      5B1
      6C1
      7C2

       

       

      Exact_Match:

      Exact_Match
      A1
      A2
      C1


      Wild_Match:

      Wild_match
      B

       

       

      Matched flag should be populated with a 1 or a 0.

       

      It should be populated with a 1 if there is an exact match in from the Code field in the data table to the exact_match field in the exact_match table.

       

      OR

       

      It should be populated with a 1 if there is an wild match in from the Code field in the data table to the wild_match field in the wild_match table.



      And I cannot simply add values in the backend of the application to a match or wild match function because those people updating the exact_match and wild_match tables will not have access to the qlik script


       

       

       

       

      I already have a solution to do this which is the following

       

      1. Use a concat() function to create a sting of all the values for both the exact_match and wild_match fields.
      2. create  2 variables using using peek function and the fields created in step 1
      3. use a match or wild match function on the variables and the code table

       

       

       

      I am wondering if there is an easier way to do this. It seems like it is complicated.

        • Re: Using a list of values to create a flag using match and wild match
          vamsee duggirala

          Hi James,

           

          I couldn't understand the part of user's updating the match and wildmatch tables. Hoping that they would be updating them in a different file which is being read as a source.

          I have implemented the data sources in the form of Inline tables.

           

          Qlik Mapping does not take wild cards. Exact Match can be implemented using ApplyMap directly, but for WildMatch considering the data you provided for Wild Match Table

           

          Try

           

          Exact_Match_Map:
          Mapping LOAD Exact_Match, 1 as Flag;
          LOAD * Inline [
          Exact_Match
          A1
          A2
          C1
          ]
          ;
          Wild_Match_Map:
          Mapping LOAD  Wild_match,1 as Flag;
          LOAD * Inline [
          Wild_match
          B,
          C
          ]
          ;


          Data:
          LOAD
          ID,
          Code,
          ApplyMap('Exact_Match_Map', Code,0) as Exact_Matched_Flag,
          ApplyMap('Wild_Match_Map', purgechar(Code,'0123456789') ,0) as WildMatched,
          ApplyMap('Exact_Match_Map', Code, ApplyMap('Wild_Match_Map',purgechar(Code,'0123456789') ,0)) as Matched_Flag

           

          /** Final Required Flag **/

           


          ;
          LOAD * INLINE [
          ID, Code, Matched_Flag
          1, A1,
          2, A2,
          3, A3,
          4, A4,
          5, B1,
          6, C1,
          7, C2,

          ]
          ;