topic Re: Replacing only character in a specific location on the string via qv load in QlikView

Replacing only character in a specific location on the string via qv load

Anonymous — Wed, 20 Jan 2016 23:45:42 GMT

hello

I am doing a qv load and in the load I am loading a set of codes for items.

the codes either start with a D or a WD then

I need to replace it with an M if D is position one..

for this one I got the if statement but the replace is replacing all the D in the code.. I just want the first one.. and im not sure why its not working below.

if(left(code,1)='D',replace(code,(left(code,1)),'M')) as code2

or delete the D if its in the 2nd position and code length greater that 11.

not sure how to do part 2..

if(left(code,2)='D',(if(count,code)=11)

thanks

Re: Replacing only character in a specific location on the string via qv load

sunny_talwar — Thu, 21 Jan 2016 10:42:03 GMT

May be this for Part 1:

If(Left(code, 1) = 'D', 'M' & Right(code, (Len(code) - 1)))

Re: Replacing only character in a specific location on the string via qv load

sunny_talwar — Thu, 21 Jan 2016 10:44:26 GMT

May be this for Part 2:

If(Mid(code, 2, 1) = 'D' and Len(code) = 11, Left(code, 1) & Right(code, 9))

Re: Replacing only character in a specific location on the string via qv load

Anonymous — Thu, 21 Jan 2016 17:56:53 GMT

had to change the above to length to 12 but it works..

do you know how I can put this in one if statement?

right now when I do this.. It makes 2 columns of data..

if code 1 = column data 1

if code 2 = column data 2

how do I run both to get it into one column data 1

thanks

Re: Replacing only character in a specific location on the string via qv load

sunny_talwar — Thu, 21 Jan 2016 18:01:18 GMT

I guess which one do you want to check first? First Letter = 'D' or the Second Letter = 'D'?

If First Letter = 'D', then try this:

If(Left(code, 1) = 'D', 'M' & Right(code, (Len(code) - 1)),

If(Mid(code, 2, 1) = 'D' and Len(code) = 11, Left(code, 1) & Right(code, 9)

, ElseConditionIfNeeded

))

If Second Letter = 'D'

If(Mid(code, 2, 1) = 'D' and Len(code) = 11, Left(code, 1) & Right(code, 9)

If(Left(code, 1) = 'D', 'M' & Right(code, (Len(code) - 1))

, ElseConditionIfNeeded

))

Re: Replacing only character in a specific location on the string via qv load

Anonymous — Thu, 21 Jan 2016 18:20:23 GMT

hi sunny

everytime I do

If(Left(code, 1) = 'D', 'M' & Right(code, (Len(code) - 1)),

If(Mid(code, 2, 1) = 'D' and Len(code) = 11, Left(code, 1) & Right(code, 9) as code2)),

I get an missing ) error...

Re: Replacing only character in a specific location on the string via qv load

sunny_talwar — Thu, 21 Jan 2016 18:22:58 GMT

You probably need this:

If(Left(code, 1) = 'D', 'M' & Right(code, (Len(code) - 1)),

If(Mid(code, 2, 1) = 'D' and Len(code) = 11, Left(code, 1) & Right(code, 9))) as code2,

Re: Replacing only character in a specific location on the string via qv load

Anonymous — Thu, 21 Jan 2016 19:07:01 GMT

perfect

thank you so much