topic Re: Phone Number formatting in QlikView

Phone Number formatting

Wed, 27 Jan 2010 06:42:45 GMT

Hello!

It seems to be simple issue, but I was not able to find the answer myself, so I would need an expert advice from the forum members:

1. I have a US phone numbers field in the Table Box where the data is stored in simple numeric format: 2223334444

2. I would need it to be presented in the standard way: (222) 333-4444 or +1 (222) 333-4444, but I was not able to find the proper format in the "Number" tab of the TB Properties.

I would appreciate an advice.

Regards,

Phone Number formatting

rwunderlich — Wed, 27 Jan 2010 08:58:35 GMT

You cannot do that kind of formatting in a TableBox. You have to do the formatting in either the script or a Straight Table chart using an expression like:

'(' & left(RawPhone,3) & ')' & mid(RawPhone,4,3) & '-' & mid(RawPhone, 7)

See the attached for an example of both methods.

-Rob

Phone Number formatting

Miguel_Angel_Baeyens — Wed, 27 Jan 2010 08:59:08 GMT

Unless someone provides new and more useful information about this, I'm afraid you should create your own format with string functions like

'+1 (' & Left(PhoneField, 3) & ') ' & Mid(PhoneField, 4, 3) & '-' & Right(PhoneField, 4) AS PhoneField

in your load script or something similar anywhere in your graphs, as a phone number is not really a number (you don't add or substract one from another)

Phone Number formatting

Wed, 27 Jan 2010 15:18:58 GMT

Thank you, Rob and Miguel, it works perfectly!

Regards,

Re: Phone Number formatting

swathid1 — Fri, 12 Sep 2025 13:52:22 GMT

Hi,

I am new to Qlik and finding it hard to follow formatting and unable to access PhoneFormat.qvw from Qlik Online. Could you please explain.

My data has phone #as follow:

030-0074321

(5) 555-4729

(171) 555-7788

0921-12 34 65

88.60.15.31

0621-08460

....

Regards,

Swathi

Re: Phone Number formatting

BrunPierre — Mon, 15 Sep 2025 09:08:13 GMT

@swathid1 What's your expected output?

Re: Phone Number formatting

HirisH_V7 — Mon, 15 Sep 2025 12:45:28 GMT

Hi , This may result in only numbers. Instead if you're looking certain output, do mention same.

KeepChar(PhoneNumber, '0123456789') AS PhNO_NumericOnlyField

Re: Phone Number formatting

swathid1 — Mon, 15 Sep 2025 15:52:48 GMT

Thank you Hirish. This is what i used. Anyway i am suck with Date formatting now.

my column has multiple formats, i would like to keep them in DD/MMM/YYYY Australian format:

Order Date

39451

39203

2008-11-02

02/04/2008

03/10/2008

24/11/2008

04/24/2008

04/10/2008

05/12/2008

05/12/2009

05/09/2009

05/01/2009

05/02/2008

05/04/2008

05/08/2008

05/08/2007

05/10/2008

Much appreciate any help regarding this.

Re: Phone Number formatting

HirisH_V7 — Tue, 16 Sep 2025 07:51:58 GMT

You can use something like this, by including your more date formats.

Date(
Alt(
Date#([Order Date],'YYYY-MM-DD'),
Date#([Order Date],'DD/MM/YYYY')
)
,'DD/MM/YYYY') as [Final Order Date]