10 Replies Latest reply: Dec 30, 2013 2:01 PM by cl bld RSS

    Change value if matching condition



      I would like to clean up the data directly in QLikView.


      For instance, I would like to merge the data who have apparently same billing account, but expressed a different way,


      ex. billing account has the values Company LTD and Company LIMITED, I would like to rename billing account to Company LTD when it is Company LIMITED.


      is it possible to achieve so ?


      where should script be done:

      - in Expression tab

      - in the Edit Script



      could you propose a script for this simple case? more precisely, Sales Accounts should match exactly, and Billing Account should be * LTD or * LIMITED.


      I am working with pivot table



        • Re: Change value if matching condition
          Bill Markham



          Have you tried using the mapsubstring() function in the load script ?



          Best Regards,     Bill

          • Re: Change value if matching condition
            Gysbert Wassenaar

            You can use a mapping table in combination with the mapsubstring function.

            From the help file:

            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.



            // Assume the following mapping table:


            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>'


            Your mapping table would look something like:



            mapping load * inline [


            LTD, LIMITED

            ...other mappings here...


              • Re: Change value if matching condition

                thaks a lot, this is helpful!


                where do i write the mapString command then ? in the load script also ? in a separate script ?


                i have an idea that i can write in the load file the following


                LOAD Country,

                     [Corporate Account],

                     MapSubString(CleanUpMap,[Sales Account]),

                     MapSubString([Billing Account])



                (biff, embedded labels, table is Account$);