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

    Change value if matching condition

      All,

       

      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

       

      Thanks

        • Re: Change value if matching condition
          Bill Markham

          Hi

           

          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.

             

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

             

            Your mapping table would look something like:

             

            Map1:

            mapping load * inline [

            x,y

            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])

                FROM

                [F:\Ex3\Revenue.xls]

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