topic Formating Telephone numbers in QlikView

Formating Telephone numbers

Mon, 05 Jul 2010 12:52:07 GMT

Hi all. I got a problem. I'm new to Qlikview but I would like to know if there is some way to edit the number format when it has already been put into Qlikview as i do not have the raw data anymore. If so, please explain how but remember as I mentioned, I am new to Qlikview

Thanks so much

chris@obi.co.za

Formating Telephone numbers

Miguel_Angel_Baeyens — Mon, 05 Jul 2010 12:56:01 GMT

Hello Chris,

You may use within a chart or as calculated dimension something like (example)

Num(PhoneNumberField, '(##) ## - ####')

Hope that helps

Formating Telephone numbers

Tue, 06 Jul 2010 08:38:31 GMT

Hey man.

Thanks for the reply but unfortunately I've tried that and it doesn't work. Got any other ideas?

Formating Telephone numbers

Tue, 06 Jul 2010 08:45:26 GMT

If I use the calculated dimension you mentioned, I get this...

Formating Telephone numbers

Miguel_Angel_Baeyens — Tue, 06 Jul 2010 08:53:08 GMT

Chris,

I was wrong then, you will have to divide your figure, kind of

='(' & Left(PhoneField, 2) & ') ' & Mid(PhoneField, 3, 3) & ' - ' & Right(PhoneField, 4)

Formating Telephone numbers

Tue, 06 Jul 2010 09:00:56 GMT

Miguel

Nope thats also not working. Theres no numbers at all only brackets.

Formating Telephone numbers

Tue, 06 Jul 2010 09:07:19 GMT

='(' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,4),2) & ' ' & right(left(PhoneNumber,6),2) & ' - ' & right(left(PhoneNumber,10),4)

Will change 1234567890 (12) 34 56-7890

Anders

Formating Telephone numbers

Tue, 06 Jul 2010 09:29:24 GMT

Hi Ansch

Well that worked. Thanks for the help

Formating Telephone numbers

Tue, 06 Jul 2010 11:04:25 GMT

Theres just on more lil issue with that formula though, it makes a double of the 7th number and cnt seem to get it to subtract one. got any ideas?

Thanks

Formating Telephone numbers

Miguel_Angel_Baeyens — Tue, 06 Jul 2010 11:10:04 GMT

Hello,

Can you please show us one original phone number and how the result would look like? It would be very useful.

Formating Telephone numbers

Tue, 06 Jul 2010 11:15:35 GMT

Formating Telephone numbers

Tue, 06 Jul 2010 11:19:56 GMT

You have only 9 numbers, my code was for 10 numbers.

Try this code

='(' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,4),2) & ' ' & right(left(PhoneNumber,6),2) & ' - ' & right(left(PhoneNumber,9),3)

It will give (12) 34 56 - 789

Anders

Formating Telephone numbers

Tue, 06 Jul 2010 11:22:55 GMT

that works but have you got a formula to change it from 123456789 to (012) 345-6789 maybe? Would be very helpful

Formating Telephone numbers

Tue, 06 Jul 2010 11:52:40 GMT

Try this

='(0' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,5),3) & ' - ' & right(left(PhoneNumber,9),4)

Anders

AW:Re: Formating Telephone numbers

brenner_martina — Tue, 06 Jul 2010 11:54:02 GMT

Hi,

123456789 to (012) 345-6789

='(0'&Left(PhoneNumber,2)&') '&Mid(PhoneNumber,3,3)&'-'&
Right(PhoneNumber,Len(PhoneNumber)-5)

AW:Re: Formating Telephone numbers

Tue, 06 Jul 2010 13:19:46 GMT

Thanks for the Dimension but theres one problem. Some of the numbers are not filled in so they come out as (0) -. How do i change it to say either 'Incomplete' or to display nothing at all?

Sorry for asking so much

AW:Re: Formating Telephone numbers

Tue, 06 Jul 2010 13:56:07 GMT

You can check if it is 9 numbers.

=if(len(PhoneNumber) = 9,'(0' & left(PhoneNumber,2) & ') ' & right(left(PhoneNumber,5),3) & ' - ' & right(left(PhoneNumber,9),4),'Incomplete')

Anders

AW:Re: Formating Telephone numbers

Tue, 06 Jul 2010 14:26:28 GMT

thanks so much anders, it worked like a charm. I was wondering, can I do the same with a field if the value is 13 digits long and no spaces. so it appears as 1234567890123 for example?

AW:Re: Formating Telephone numbers

Tue, 06 Jul 2010 14:32:26 GMT

Change the 9 to 13

=if(len(PhoneNumber) = 13,...........

AW:Re: AW:Re: Formating Telephone numbers

brenner_martina — Tue, 06 Jul 2010 14:47:07 GMT

Hi,

this expression is for every phonenumber larger then 5 digits!

=If(Len(PhoneNumber)>0,'(0'&Left(PhoneNumber,2)&') '&Mid(PhoneNumber,3,3)&'-'&
Right(PhoneNumber,Len(PhoneNumber)-5))