topic Re: How to insert "_" after a specific letter in QlikView

How to insert "_" after a specific letter

pra_kale — Wed, 01 Aug 2018 16:13:34 GMT

Hi,

In my data in 1 column I have member numbers in different formats like '01ED2', '01EF3' ,'01E12','0E245','039E9','12345E12','E1234' etc

I want to insert "_" only if after E there is a number else member number will remain as it is..for e.g I will get out-put like this

01ED2', '01EF3' ,'01E_12','0E_245','039E_9','12345E_12','E_1234'

By below given function I able to found whether string is having "E" in it but finding difficult to set other conditions.

If( FindoneOf(MEMBER,'E') ,'Found it','Not Found')

Thanks in Advance

‌

sunny_talwar — Wed, 01 Aug 2018 16:25:37 GMT

May be this

If(IsNum(Left(SubField(FieldName, 'E', 2), 1), SubField(FieldName, 'E', 1) & 'E_' & SubField(FieldName, 'E', 2), FieldName)

sunny_talwar — Wed, 01 Aug 2018 16:28:15 GMT

My bad, try this

Table:

LOAD *,

If(IsNum(Left(SubField(FieldName, 'E', 2), 1)), SubField(FieldName, 'E', 1) & 'E_' & SubField(FieldName, 'E', 2), FieldName) as NewFieldName;

LOAD * INLINE [

FieldName

01ED2

01EF3

01E12

0E245

039E9

12345E12

E1234

];

shahenda_ebead — Wed, 01 Aug 2018 16:34:40 GMT

Do you expect to see the E letter more than once in your field string or it will be just there once

pra_kale — Wed, 01 Aug 2018 17:40:33 GMT

Thanks Sunny...It is perfectly working..