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

[tresesco]

You can change the used characters used. Just think of a character that should not appear in your string and use that instead of '\' and '/'.

May be like:

'~~' & [Replace String] & '^^' 

....

TextBetween(MapSubString('excel2', Name), '~~', '^^'))

[vignesh_s]

hi thanks for ur rply,but my trainer told what delimiter u give , the same thing i ll give in the input file he told,can u give an alternate expresion

[tresesco]

Your trainer must be asking you to try yourself hard so that you can learn it better and may be find even a better solution. So, let's not defeat his purpose of giving task. If you can't; ask him/her; will get the alternate solution. Or, if he/she too is not sure about it, let us know here, will give another try.

[vignesh_s]

ok thanks

[vignesh_s]

but im trying from 3days but the result is 0

[vignesh_s]

i tried with one more load im geting wrong output

[tamilarasu]

Hi Vignesh,

Not elegant but thought of sharing with you.

excel2:
LOAD Capitalize(Name) as Name,
[Replace String]
INLINE [
    Name, Replace String
    ball, Bat is best because of batman
    dog, is not
    eli, Vadivel padam
    apple, Android is best
    xmas, pongal tha gethu
];

Map1:
Mapping Load Name, [Replace String]
Resident excel2;

Map2:
Mapping Load Name, '' as Dummy
Resident excel2;

excel1:
LOAD *,
If(MapSubString('Map1', Name)=Name, Name, Replace(MapSubString('Map1', Name), Ltrim(MapSubString('Map2', Name)),'' ))as StringAfterReplace
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
];

DROP Table excel2;

Output:

[vignesh_s]

hi thanks ,the expression is not visible fully,can u please provide qvd

[tamilarasu]

Attached.

[sunny_talwar]

Although I agree with tresesco‌ and I don't understand your trainer's intention... I still offer you a solution which doesn't use MapSubString

excel2:

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

];

excel1:

LOAD *,

SubField(Name, ' ') as String

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

];

FinalTable:

NoConcatenate

LOAD Name,

Name as Check,

String

Resident excel1;

Right Join (FinalTable)

LOAD *

Resident excel2;

Concatenate (FinalTable)

LOAD Name

Resident excel1

Where not Exists(Check, Name);

DROP Tables excel2, excel1;