2 Replies Latest reply: Mar 15, 2014 4:39 PM by Nagaian Krishnamoorthy RSS

    Is it possible to wildmatch data from two tables?

      Hi,

      I'm new to Qlikview so I would like to consult some questions and would appreciate if u guys can help.

      Is it possible to use WildMatch() function for both Customer and Address table given the following tables?

      I would like to get the customer address from Address table if CustId has match with Id.

      Expected result will be getting address for 12aazz,34bbxx and 56ccyy.

      Like WildMatch(CustId,Id) works or is there any other way to get the same result?

       

      Customer

      CustIdName
      12aazzJason
      34bbxxAlice
      56ccyyJames
      78ddwwAlice

       

      Address

      IdAddress
      aazzxxxxxxxxxxxxxxxx
      34bbyyyyyyyyyyyyyyyy
      8ddwzzzzzzzzzzzzzzzz

       

      Please help me.

      Thank you.

        • Re: Is it possible to wildmatch data from two tables?
          Gysbert Wassenaar

          Have a look at the mapsubstring function:

           

          The MapSubstring function is used to map parts of any expression to a previously loaded mapping table. The mapping is case sensitive and non-iterative and substrings are mapped from left to right. The syntax is:

           

          mapsubstring('mapname', expr)

          This function can be used for mapping parts of any expression on a previously loaded mapping table. The mapping is case sensitive and non-recursive. The substrings are mapped from the left to the right. Mapname is the name of a mapping table previously read by a mapping load or a mapping select statement (see Mapping). The name must be enclosed by single straight quotation marks. Expr is the expression whose result should be mapped by substrings.

          Examples:

          // Assume the following mapping table:

          map1:

          mapping load * inline [

          x, y

          1, <one>

          aa, XYZ

          x, b ] ;

           

          MapSubstring ('map1', 'A123') returns 'A<one>23'

          MapSubstring ('map1', 'baaar') returns 'bXYZar'

          MapSubstring ('map1', 'xaa1') returns 'bXYZ<one>'

          • Re: Is it possible to wildmatch data from two tables?
            Nagaian Krishnamoorthy

            For your sample data, the following script gives the result. (This may not be a desired solution when the number of rows in each table is large.)

             

            Temp:

            LOAD * Inline [
            CustId,Name
            12aazz,Jason
            34bbxx,Alice
            56ccyy,James
            78ddww,Alice
            ]
            ;
            Outer Join (Temp)
            LOAD * Inline [
            Id,Address
            aazz,xxxxxxxxxxxxxxxx
            34bb,yyyyyyyyyyyyyyyy
            8ddw,zzzzzzzzzzzzzzzz
            ]
            ;

            Result:
            NoConcatenate
            LOAD CustId, Name, Id, Address Resident Temp
            Where Index(CustId, Id) > 0;

            DROP Table Temp;

             

            The qvw file is  attached.