topic Re: Syntax Problem in QlikView

Syntax Problem

tmumaw — Thu, 19 Nov 2015 17:08:07 GMT

Hello everyone,

I am having an issue with my syntax and was wondering if someone could help me. I only want the first 13 positions. What am I doing wrong? Thanks

if(len(Address2_Telephone1) >= 12,

(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13))) as New_Address2_Telephone1,

Re: Syntax Problem

sunny_talwar — Thu, 19 Nov 2015 17:11:15 GMT

Would be best if you can share 2-3 data lines. Also I notice you don't have a false condition?

if(len(Address2_Telephone1) >= 12,

(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13)), Address2_Telephone1) as New_Address2_Telephone1,

Re: Syntax Problem

swuehl — Thu, 19 Nov 2015 17:16:41 GMT

Maybe you also want to review your order of replace statements, maybe the replace of ' ' (space) with an empty string '' should be last when executing inner to outer Replace functions.

You may also want to consider using PurgeChar() function.

Re: Syntax Problem

tmumaw — Thu, 19 Nov 2015 17:18:43 GMT

the above syntax is giving me more than 13 positions in my output row. Example: (330)876-5412x998 I only want the first 13 positions (330)876-5412. Hope this helps.

Re: Syntax Problem

sunny_talwar — Thu, 19 Nov 2015 17:20:22 GMT

What if you do:

Left(Address2_Telephone1, 13) as Address2_Telephone1

Re: Syntax Problem

swuehl — Thu, 19 Nov 2015 17:27:42 GMT

Your original script seems to work for me:

LOAD *,

if(len(Address2_Telephone1) >= 12,

(Mid(Replace(Replace(Replace(Address2_Telephone1,' ',''),Chr(10),' '),Chr(13),' '),1,13))) as New_Address2_Telephone1;

LOAD * INLINE [

Address2_Telephone1

(330)876-5412x998

];

Re: Syntax Problem

tmumaw — Thu, 19 Nov 2015 17:33:59 GMT

I don't want the x998 at the end. I only want this (330)876-5412

Re: Syntax Problem

sunny_talwar — Thu, 19 Nov 2015 17:35:07 GMT

This isn't working?

Left(Address2_Telephone1, 13) as Address2_Telephone1

Re: Syntax Problem

settu_periasamy — Thu, 19 Nov 2015 17:37:23 GMT

May be this

left (keepchar (Address2_Telephone1

,'0123456789 ()-'),13) as New_Address2_Telephone1

Re: Syntax Problem

sunny_talwar — Thu, 19 Nov 2015 17:38:36 GMT

I think if he does this, then may be just 10?

left (keepchar (Address2_Telephone1

,'0123456789 ()-'),10) as New_Address2_Telephone1

Re: Syntax Problem

swuehl — Thu, 19 Nov 2015 17:45:56 GMT

And this is exactely what I get:

New_Address2_Telephone1
(330)876-5412

Re: Syntax Problem

settu_periasamy — Thu, 19 Nov 2015 17:52:41 GMT

I calculated including ( ) -.

So, i think it would be 13..but not sure..

Re: Syntax Problem

sunny_talwar — Thu, 19 Nov 2015 17:54:28 GMT

Sorry I just didn't notice the ()-. You are right.