topic Re: Replace with String Contains in QlikView

Replace with String Contains

settu_periasamy — Thu, 21 May 2015 03:57:19 GMT

Hi,

I need to replace the string based on between the values(like textbetween). see the example below.

Need to replace the String ': * |' as blank. (note : '*' contains any length)

FieldOne: raja | FieldTwo: India | FieldThree: Calculated |

result should be :

FieldOne FieldTwo FieldThree

i tried the below expression

'=replace('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |',':'&chr(42)&'|',' ')

Thank You.

Re: Replace with String Contains

ToniKautto — Thu, 21 May 2015 04:18:59 GMT

That's gonna be hard in a sheet object. I would suggest doing it in the script, so your data is pre-formatted for presentation.

LOAD
  SubField(F1, ':', 1) AS F1
;
LOAD 
  SubField(F1, ' | ')  AS F1
Inline [
F1
FieldOne: raja | FieldTwo: India | FieldThree: Calculated |
];

Re: Replace with String Contains

senpradip007 — Thu, 21 May 2015 04:20:24 GMT

Try with

=PurgeChar('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ':|')

Re: Replace with String Contains

danieloberbilli — Thu, 21 May 2015 04:23:57 GMT

LOAD *,

left(F1, Index(F1,':',1)-1) &' '&

mid(F1,Index(F1,'|',1)+1,Index(F1,':',2)-Index(F1,'|',1)-1 ) &' '&

mid(F1,Index(F1,'|',2)+1,Index(F1,':',3)-Index(F1,'|',2)-1 ) as Result

;

LOAD * INLINE [

FieldOne: raja | FieldTwo: India | FieldThree: Calculated |

];

Re: Replace with String Contains

jagan — Thu, 21 May 2015 04:29:55 GMT

Hi,

Try like this in script

Temp:

LOAD

RecordNum,

RowNo() AS Num,

SubField(F1, ':', 1) AS F1

;

LOAD RecNo() AS RecordNum,

SubField(F1, ' | ') AS F1

Inline [

FieldOne: raja | FieldTwo: India | FieldThree: Calculated |

];

Data:

LOAD

RecordNum,

CONCAT(F1, ' ', Num) AS Value

RESIDENT Temp

GROUP BY RecordNum

ORDER BY Num;

DROP TABLE Temp;

OR if you want to do it in front end try like this

SubField(SubField('FieldOne: raja | FieldTwo: India | FieldThree: Calculated |', ' |', 3), ':', 1)

Hope this helps you.

Regards,

Jagan.

Re: Replace with String Contains

settu_periasamy — Thu, 21 May 2015 04:50:51 GMT

Thanks Jagan.

I want to do this in front end. The given expression is fine.

But if I have Large String, is any other function is available?

Regards,

Sethu

Re: Replace with String Contains

settu_periasamy — Thu, 21 May 2015 04:51:53 GMT

Hi,

I tried this. But not getting the expected result.

Re: Replace with String Contains

ToniKautto — Thu, 21 May 2015 05:01:49 GMT

I assume the long string comes from the loaded data, so why do you want to process it in run-time instead of pre-processing it during reload?

I realized I forgot to add the concat in my example.

LOAD Concat(F1) AS F1;
LOAD SubField(F1, ':', 1) AS F1;
LOAD SubField(F1, ' | ')  AS F1
Inline [
F1
FieldOne: raja | FieldTwo: India | FieldThree: Calculated |
];

Re: Replace with String Contains

danieloberbilli — Thu, 21 May 2015 05:03:24 GMT

My example from above also works in the frontend...just type the following expression in a textbox or chart and replace the field name

left(F1, Index(F1,':',1)-1) &' '&

mid(F1,Index(F1,'|',1)+1,Index(F1,':',2)-Index(F1,'|',1)-1 ) &' '&

mid(F1,Index(F1,'|',2)+1,Index(F1,':',3)-Index(F1,'|',2)-1 )

Re: Replace with String Contains

settu_periasamy — Thu, 21 May 2015 05:11:52 GMT

Thanks Toni. I will try to implement this in the script.

Re: Replace with String Contains

settu_periasamy — Thu, 21 May 2015 05:14:11 GMT

It is working fine Daniel. But the problem is , Only three strings displayed.

Thank you. I'm closing this Discussion

Re: Replace with String Contains

jagan — Thu, 21 May 2015 05:19:15 GMT

Hi Sethu,

Copy the string into a variable and use that variable in the expression. If your string is dynamic like 4, 5 , 6 values then trying this in script is the best option.

Regards,

Jagan.

Re: Replace with String Contains

settu_periasamy — Thu, 21 May 2015 05:21:39 GMT

Ok Jagan. I will do like that.