Change value if matching condition

[#1]

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

[Anonymous]

Hi

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

Best Regards,     Bill

[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...

];

[#4]

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

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

[Gysbert_Wassenaar]

In the load script as well in the load statement that loads the field you want to apply the mapping to.

[#6]

my quore above is not compiling. what would you  write then ? error is that MapSubString takes constant map as first argument...

thanks again for your time

[Gysbert_Wassenaar]

The name of the map needs to be enclosed in single quotes:

LOAD Country,

     [Corporate Account],

     MapSubString('CleanUpMap',[Sales Account]) as [Sales Account],

     MapSubString('CleanUpMap',[Billing Account]) as [Billing Account]

FROM

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

[#8]

LOAD Country,

     [Corporate Account],

     [Sales Account],

     MapSubString('CleanUpMap',[Billing Account]) as [Billing Account]

FROM

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

is compiling, but not doing the clean up it should, as the mapping is defined as

CleanUpMap:

mapping load * inline [

LTD, LIMITED

];

in your example, second argument for mapSubString is the text field where you wish to do the match. how is it if you want to perform the match on the whole vector of field values ?

[Gysbert_Wassenaar · Accepted solution]

1. The mapping table must be created before it can be used

2. The first line in an inline load contains the field names of the table. So your mapping table is empty since it contains only the field names.

3. Keep in mind that Qlikview is case sensitive so LTD will be replaced by LIMITED, but Ltd will not. Add additional records to the mapping table if you need to map for example Ltd to LIMITED too.

[#10]

thanks

in the meantime, i found

LOAD Country,

     [Corporate Account],

     [Sales Account],

     Replace([Billing Account],'LIMITED','LTD') as [Billing Account]

FROM

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

which is working fine.

i will try again with the map.