replace string

[vignesh_s]

hi all i have 2 excel file,1st 1 is String and 2nd excel contains which string should replaced with what

ex:

  excel1:

Name

My name is Fund

A for Apple

Apple is a fruit

C for Cat

Fund is a Vlaue

D for Dog

F for Fund

E for Eli

Xmas is on 25 dec

excel2:

String	Replace String
ball	Bat is best because of batman
dog	is not
eli	Vadivel padam
apple	Android is best
xmas	pongal tha gethu

i need scrit such that it should work dynamicaly,means what ever i give in 2nd excel (replace string) should reflect in out put by just doing reload.Can any1 help

[vignesh_s]

Thanks Sunny ,this what i was expecting

[vignesh_s]

Hi sunny it works if string contains single world,what if 'sentence' have given to replace

for example:

i have Apple should b replaced with Andriod is best

what if i have

"Apple is" should b replaced with Andriod is best,that tym this code  is nt working

[sunny_talwar]

Then my friend, you are out of luck.... you can throw in 500 different things out there and we might be able to give you 500 different answer, but there will always be a 501th IF for which I might not have a answer for you. Do you even know what the real requirement is? or is this just to learn? may be we can have a better discussion if we know what exactly you are trying to do and what are your constraints instead of looking at 501 if statements

[vignesh_s]

hi sunny, im learning qlik view ,my requirment is i have 2 xsl file ,0ne is with strings

the other file contain which string should replaced with what

[sunny_talwar]

This all makes sense, but you can't use MapSubString() because it could have all possible delimiters? You can't use the second solution I provided because you have spaces between your strings? Can you use Tamil's solution? or does that have problems too? I know his is also using MapSubString(), but may be that works?

[vignesh_s]

Actuly tamil's solution works proporly,but my trainee needs ,without using maping,i wanna use only joins and wild match

[sunny_talwar]

He is just a trainer, right? Why don't you ask him what he has in mind and share it with all of us? I would love to learn another way to do this

[vignesh_s]

he told to use wild match and joins exits function ,it make take more than 4 loads also r more than that

[sunny_talwar · Accepted solution]

I would not recommend using this, but here you go...

excel1:

LOAD * INLINE [

    Name

    My name is Fund

    A for Apple

    Apple is a fruit

    C for Cat

    Fund is a Vlaue

    D for Dog

    F for Fund

    E for Eli

    Xmas is on 25 dec

];

Temp:

NoConcatenate

LOAD *

Resident excel1;

Join (Temp)

LOAD Capitalize(String) as String,

[Replace String]

INLINE [

    String, Replace String

    ball, Bat is best because of batman

    dog, is not

    eli, Vadivel padam

    apple, Android is best

    xmas, pongal tha gethu

];

FinalTable:

LOAD Name,

Name as TempName,

[Replace String]

Resident Temp

Where WildMatch(Name, '*'&String&'*');

Concatenate(FinalTable)

LOAD Name

Resident excel1

Where Not Exists(TempName, Name);

DROP Table excel1, Temp;

[sunny_talwar]

If he still doesn't like this... I would suggest you to ask him for the script because we cannot look inside his mind to see what he has in mind . It's better he/she share what they have in there mind rather than all of us wrestling