replacing a letter with a word

[Anonymous]

Hi Guys,

I want to replace a letter with a word in Qlikview, However when I use the replace statement, It replaces everything that has the letter "R".

I first tried:

Upper(Replace(shpg, 'R', 'Road)) That replaced all the words that have the letter "R".

I then tried it with an if statement:

if(shpg<>'R',

Upper(replace(shpg, 'R', Road))

That to didn't work.

Basically I want to replace only the fields that have 'R' with Road.

Hope u can help me!

Thanks in advance!

iSam

[swuehl · Accepted solution]

Not sure if I fully understood..

In a field shpg, you want to replace a single letter 'R' with 'Road', but leave every other Word containing 'R' or just starting with 'R' untouched?

Like

'R'  --> 'Road'

'noRmal' --> 'noRmal'

'Roadrunner' --> 'Roadrunner'

For just a single letter, i would try

Load

....

if(trim(shpg) = 'R', 'Road', shpg) as YourNewField,

..

from ...

You could also use a mapping table, but for one single letter replacement, I think it's not necessary.

Hope this helps,

Stefan

[Anonymous]

Hi stefan,

Sorry for being unclear.

Have a look at the screenshot below J:

I only want to change “R” in the Shpg Field and leave everything the way it is. That should result in: AIR, COURIER, ROAD, SEA, TRUCK,

By using the expression I used: replace(Shpg, ‘R’, ‘ROAD’) everything with a ‘R’ is replaced.

Hope u can help!

Thanks for your quick response btw!!

Cheers

Isam

[swuehl]

I think my above posted line should just do that for you, have you tried it?

Regards,

Stefan

[Anonymous]

Unfortunately not. Your expression excludes all the value and shows the only ‘ROAD’ L

[swuehl]

Hm, can't confirm this, below code just worked fine for me (or I do miss something):

REPLACE:

LOAD * Inline [

shpg

AIR

COURIER

R

SEA

TRUCK

];

LOAD *,

if(trim(shpg) = 'R', 'ROAD', shpg) as YourNewField

resident REPLACE;

[#7]

Hi Sam,

You could do something like this:

if(shpg='R','Road',shpg) as YourNewField

This should group R values with Road values.

Cheers,

Shyam

[perumal_41]

Hi,

Please upload ur File .

[stevedark]

The use of the resident field in your example is not actually required, this will do the same:

Data:

LOAD

     if(trim(shpg) = 'R', 'ROAD', shpg) as shpg

Inline [

shpg

AIR

COURIER

R

SEA

TRUCK

];

Obviously to put it in the context of the original poster, the only the if line is required in place of the current shpg line in the load script.

[stevedark]

Personally, I always find the most flexible way is to use an ApplyMap statement.  The hows and why's can be found here: http://bit.ly/kQcAZ5

- Steve