topic Re: How to set the script to get correct value in table box? in QlikView

How to set the script to get correct value in table box?

Fri, 05 Dec 2014 09:58:12 GMT

I have data in table box for Number and Mobile.

Number

087-7092208
016267007
040466943
0860647091
0872761125
0282718
N/A
085 7041865
0047653866017203
01-2889533
n/a

Mobile

0876746764
087-76767768
087-089566536
0756457459
04746746767
03323338768
08868578768
089076646565
004765876776768

I need to replace where ever there is 0 it should replace with '4765' i,e if the value is 0876746764 it should get output as 4765876746764. If the value is like this 004765876776768 it should delete '00' and keep only 4765876776768

when I tried below in script it is showing data as 476547654765876776768 i,e values are entering twice or the 0 is not dropping.

and where ever the value is n/a it should keep as n/a

How to change below script. Please can anyone suggest me.

[Number],
     '4765'&Replace(LTrim(Replace(KeepChar([Number], '0123456789'),'0',' ')),' ','0') as [Number_1],
     Mobile,
     '4765'&Replace(LTrim(Replace(KeepChar(Mobile, '0123456789'),'0',' ')),' ','0') as [Mobile_1]

Thanks.

Re: How to set the script to get correct value in table box?

Fri, 05 Dec 2014 10:14:28 GMT

Any help please

Re: How to set the script to get correct value in table box?

rubenmarin — Fri, 05 Dec 2014 10:22:54 GMT

Hi Amelia, hope this helps:

If(Left(KeepChar(Number, '123456789'), 4)=4765, Mid(Number, index(Number, '4765')),

If(Left(Number,1)=0, '4765' & Mid(Number, 2), Number)

)

Re: How to set the script to get correct value in table box?

tresB — Fri, 05 Dec 2014 10:24:33 GMT

May be like:

'4765'&Replace(Left([Number],2),'0','') & KeepChar(Mid([Number],3), '0123456789') as [Number_1],

Re: How to set the script to get correct value in table box?

Anonymous — Fri, 05 Dec 2014 10:49:22 GMT

Hi Amelia

try this script

Attached is the application

Assuming Number is your Field and chainge 'Zero' to respective number

Test:

LOAD text(Number) as Number

FROM

C:\Desktop\DimensionMme.xlsx

(ooxml, embedded labels, table is Sheet1);

for i = 1 to NoOfRows('Test')

let number=FieldValue('Number',$(i));

for j= 1 to len('$(number)')

if(left('$(number)',$(j))>0)then

if($(j)=1)then

let final_number = '$(number)';

exit for

ELSE

let final_number=Replace('$(number)', left('$(number)',$(j)-1),'Zero');

exit For

end if

if($(i)=1)then

Final:

load

'$(final_number)' as final_number

Resident

Test;

else

Concatenate(Final)

LOAD

'$(final_number)' as final_number

Resident

Test;

ENDIF

Regards

Harsha

Re: How to set the script to get correct value in table box?

PrashantSangle — Fri, 05 Dec 2014 11:34:46 GMT

Hi,

Try like

if(wildmatch(number,'00*'),replace(number,'00',''),

if(wildmatch(number,'0*'),replace(number,'0','4765'),number)

)

Regards

Re: How to set the script to get correct value in table box?

Mon, 08 Dec 2014 11:39:05 GMT

Thanks. It is working. one more request please. In the same data how can I remove in between spaces, bracets, '-'

Please suggest me.

476587-73539223538

476585 735341865

4765 (0) 872686352

Re: How to set the script to get correct value in table box?

jerrysvensson — Mon, 08 Dec 2014 11:48:09 GMT

purgechar()

Re: How to set the script to get correct value in table box?

PrashantSangle — Tue, 09 Dec 2014 05:33:44 GMT

Hi,

As Jerry suggested you can use Purgechar() or

you can try with keepchar() also.

Like Keepchar(FieldName,'0123456789')

Regards

Re: How to set the script to get correct value in table box?

Wed, 10 Dec 2014 09:38:55 GMT

Thanks. when am using this it is going again back to original values. How can I include Keepchar(FieldName,'0123456789') in to below expression

if(wildmatch(number,'00*'),replace(number,'00',''),

if(wildmatch(number,'0*'),replace(number,'0','4765'),number)

)

Please suggest me.