topic Re: replace string in QlikView

replace string

vignesh_s — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: replace string

sunny_talwar — Tue, 09 Jan 2018 13:23:59 GMT

May be using

MapSubstring - script function ‒ QlikView

Re: replace string

tresB — Tue, 09 Jan 2018 13:24:17 GMT

Try like:

excel2:

Mapping Load Capitalize(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 *,

MapSubString('excel2', 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];

Re: replace string

vignesh_s — Tue, 09 Jan 2018 13:40:41 GMT

can u share your qvd

Re: replace string

vignesh_s — Tue, 09 Jan 2018 14:03:31 GMT

hi,i wanna replace entier string with replacing string

Re: replace string

sunny_talwar — Tue, 09 Jan 2018 14:09:29 GMT

May be this

excel2:

Mapping

LOAD Capitalize(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 *,

TextBetween(MapSubString('excel2', 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

];

Re: replace string

vignesh_s — Tue, 09 Jan 2018 14:20:53 GMT

thank you,

if the string is not matched i wanna retain the same string,the above code giving blank for that

Re: replace string

sunny_talwar — Tue, 09 Jan 2018 14:27:57 GMT

How difficult could that be for you to solve

If(Len(Trim(TextBetween(MapSubString('excel2', Name), '/', '\'))) = 0, Name, TextBetween(MapSubString('excel2', Name), '/', '\'))

Checking if the mapping gives 0 using len(Trim()) = 0... if it does, then just use Name, else use the mapping

Re: replace string

tresB — Tue, 09 Jan 2018 14:28:21 GMT

Try just putting an additional condition on substringmap() like:

....

If(MapSubString('excel2', Name)=Name, Name,

TextBetween(MapSubString('excel2', Name), '/', '\')) as StringAfterReplace

....

Re: replace string

vignesh_s — Wed, 31 Jan 2018 07:27:40 GMT

hi sunny ,sorry to open this token again, i have some issue in this expression,i.e, you have used "delimiters" for replace strings and u retrived those from textbetween,but the issue is what if the input string or replace string contains delimiters already

Re: replace string

tresB — Wed, 31 Jan 2018 07:45:22 GMT

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), '~~', '^^'))

Re: replace string

vignesh_s — Wed, 31 Jan 2018 07:53:18 GMT

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

Re: replace string

tresB — Wed, 31 Jan 2018 08:01:35 GMT

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.

Re: replace string

vignesh_s — Wed, 31 Jan 2018 08:03:21 GMT

ok thanks

Re: replace string

vignesh_s — Wed, 31 Jan 2018 08:11:02 GMT

but im trying from 3days but the result is 0

Re: replace string

vignesh_s — Wed, 31 Jan 2018 08:12:03 GMT

i tried with one more load im geting wrong output

Re: replace string

tamilarasu — Wed, 31 Jan 2018 09:14:10 GMT

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:

Re: replace string

vignesh_s — Wed, 31 Jan 2018 10:21:13 GMT

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

Re: replace string

tamilarasu — Wed, 31 Jan 2018 10:34:04 GMT

Attached.

Re: replace string

sunny_talwar — Wed, 31 Jan 2018 12:14:08 GMT

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;