Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
I think my above posted line should just do that for you, have you tried it?
Regards,
Stefan
Unfortunately not. Your expression excludes all the value and shows the only ‘ROAD’ L
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;
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
Hi,
Please upload ur File .
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.
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