Replace all instances of a string

[gerhardl]

Hi,

Is there any way that, in my load script, I can replace all instances of a certain text string e.g. 'TEST', with something else?

I mean a way without individually doing a replace on every separate field.

Can I once, at the start of the script maybe, define that each 'TEST' shouild be replave with '123'?

[its_anandrjs]

Use Replace function for this may be.

Ex:-

Replace('abccde','cc','xyz') returns 'abxyzde'

Load

Replace(FieldName,'TEST','123') as  RepField

From location;

Regards

Anand

[jagan]

Hi,

I think this is not possible, you have use Replace for each column while you are loading the data like below

Data:

LOAD

Replace(Dimension1, 'TEST', '123') AS Dimension1,

Replace(Dimension2, 'TEST', '123') AS Dimension2

'

'

'

'

FROM DataSource;

Hope it helps you.

Regards,

Jagan.

[#4 · Accepted solution]

Hi Gerhard,

'Map Using' may be an option for you, from the QV manual:

1. Map
   ... using

Themap ... using statement is used for mapping a certain field value or
expression to the values of a specific

1. mapping
   table. The mapping table is created through the Mapping (page 265) statement.

The
automatic mapping is done for fields loaded after the map ... using statement
until the end of the script

or
until an unmap statement is encountered.

The
mapping is done last in the chain of events leading up to the field being
stored in the internal table in

1. QlikView. This means that mapping is not done every time a field name is encountered as part of an expression,

but
rather when the value is stored under the field name in the internal table. If
mapping on the expression

level
is required, the Applymap() function has to be used instead.

Note!

Themap ... using statement does not work when loading QVD files in optimized
Mode.

The
syntax is:

map*fieldlist using mapname

*fieldlist is a
comma separated list of the fields that should be mapped from this point in the
script. Using *

as
field list indicates all fields. The wildcard characters * and ? are allowed in
field names. Quoting of field

names
may be necessary when wildcards are used.

mapname is
the name of a mapping table previously read in a mapping load or mapping select statement.

would save the need to do a replace on every field

Hope that helps

Joe

[gerhardl]

I knew there had to be a way! This works beautifully, thanks.

[#6]

No worries glad to help, just be careful of the note on it regarding optimised loads, so it's better for use in your QVD creation app rather than the front end

[jagan]

Hi,

Forgot Map using, try like this

Map:

Mapping LOAD

*

INLINE [

MapString, MapValue

TEST, 123];

MAP * using Map;

Data:

LOAD

*

INLINE [

Dimension1, Dimension2

TEST, 456

SAMPLE, TEST

];

Regards,

Jagan.