Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
gerhardl
Contributor II

Replace all instances of a string

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Replace all instances of a string

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

View solution in original post

6 Replies

Re: Replace all instances of a string

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

MVP & Luminary
MVP & Luminary

Re: Replace all instances of a string

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.

Not applicable

Re: Replace all instances of a string

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

View solution in original post

gerhardl
Contributor II

Re: Replace all instances of a string

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

Not applicable

Re: Replace all instances of a string

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

MVP & Luminary
MVP & Luminary

Re: Replace all instances of a string

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.