topic Re: Change value if matching condition in QlikView

Change value if matching condition

Mon, 30 Dec 2013 16:31:15 GMT

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

Anonymous — Mon, 30 Dec 2013 16:39:59 GMT

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

Best Regards, Bill

Re: Change value if matching condition

Gysbert_Wassenaar — Mon, 30 Dec 2013 16:49:34 GMT

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

Mon, 30 Dec 2013 18:25:12 GMT

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$);

Re: Change value if matching condition

Gysbert_Wassenaar — Mon, 30 Dec 2013 18:26:21 GMT

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

Re: Change value if matching condition

Mon, 30 Dec 2013 18:30:00 GMT

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

Re: Change value if matching condition

Gysbert_Wassenaar — Mon, 30 Dec 2013 18:35:08 GMT

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$);

Re: Change value if matching condition

Mon, 30 Dec 2013 18:39:43 GMT

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 ?

Re: Change value if matching condition

Gysbert_Wassenaar — Mon, 30 Dec 2013 18:50:27 GMT

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.

Re: Change value if matching condition

Mon, 30 Dec 2013 18:53:59 GMT

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.

Re: Change value if matching condition

Mon, 30 Dec 2013 19:01:44 GMT

could you say more precise what you mean with 'fields of the table' ?

i would like to have then

CleanUpMap:

mapping load * inline [

[Billing Account]

'LTD', 'LIMITED'

];

but this is not compiling.

Thanks