3 Replies Latest reply: Jun 11, 2010 7:10 PM by John Witherspoon RSS

    Like Operator

    radhika sriramoju

      Hi

      Could anybody please tell me how do i use like oprator, i have a field for which i need to filter based on first 3 letter

      usually we do something like (product like 'msm%' ) as MSM or (product like 'qsc%') as 'QCS'

      and using these MSM,QSC i need to have a group ,

      Could anybody please assit me in doing this?

      Thansk in Advance

        • Like Operator
          John Witherspoon

          You can use the wildmatch() function as a like operator:

          wildmatch(product,'msm*')

          And build your groups something like this:

          pick(wildmatch(product,'msm*','qsc*'),'MSM','QSC') as ProductGroup

          However, if you're just trying to group on the first three characters:

          left(product,3) as ProductGroup

            • Like Operator
              radhika sriramoju

              Thank you for the reply ,

              so wild match has to be used in load properties or in the select query , i am new to qlikview ...

              sorry if this is a silly question...

               



                • Like Operator
                  John Witherspoon

                  The LOAD portion of the script uses QlikView syntax and functions. The SELECT portion of the script uses SQL syntax and functions, and is passed directly to your ODBC driver to execute on your DBMS, and is NOT processed or interpreted by QlikView so far as I know.

                  LOAD
                  <QlikView syntax and functions>
                  ;
                  SQL
                  <SQL syntax and functions>
                  ;

                  So for example:

                  LOAD
                  OIID as "Order Item"
                  ,OICUST as "Customer"
                  ,OIPROD as "Product"
                  ,if(wildmatch(OIPROD,'msm*'),'MSM','Other') as "Product Group"
                  ;
                  SQL
                  SELECT
                  OIID
                  ,OICUST
                  ,OIPROD
                  FROM BLAH.ORDERITEM
                  WHERE OITYP LIKE 'ORDER*'
                  ;

                  Like wouldn't work in the load, and wildmatch wouldn't work in the select. Each uses their own syntax.