Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

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

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

Tags (3)
1 Solution

Accepted Solutions
Highlighted

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

May be this for Part 1:

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

View solution in original post

7 Replies
Highlighted

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

May be this for Part 1:

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

View solution in original post

Highlighted

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

May be this for Part 2:

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

Highlighted
Contributor

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

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

Highlighted

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

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

))




Highlighted
Contributor

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

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

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,

Highlighted
Contributor

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

perfect

thank you so much