topic AW:Re: AW:Creating the same number format in a field in QlikView

Creating the same number format in a field

Mon, 14 Sep 2009 17:14:52 GMT

Hi everyone

Please help, i've got a field with all cell fone numbers which i'm bringing in from an excel sheet, but they are some have different formats eg. one number will be formatted: 0117836431 (which is the format i want) and others would be formatted: 011-2345765, 011 365 2365, 011543 7896 etc. In the script, how do i make them all the same format??

AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 17:18:00 GMT

Hi,

have you tried loading using text(), e.g. text(cell_fone_number)?

Rgds,
Joachim

AW:Creating the same number format in a field

Mon, 14 Sep 2009 17:23:58 GMT

Ok i've just tried text(cellnumber) as cellnumber in the script, reloaded it and still get brackets and commas etc in that field, i want to try and get all the numbers to look the same...

AW:Re: AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 17:27:45 GMT

Hi,

then I'd additionally work with MapSubstring (see reference manual). You can list the unwanted characters and the replace string (can be blank or null) there. Thus you could eliminate brackets, commas or whatsoever.

Rgds,
Joachim

AW:Re: AW:Creating the same number format in a field

Mon, 14 Sep 2009 17:34:13 GMT

Ok is there not something a bit simpler to use like the text function u mentioned??

AW:Re: AW:Re: AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 17:46:59 GMT

Yes, of course, you could edit the excel sheet 😉

Rgds,
Joachim

AW:Re: AW:Re: AW:Creating the same number format in a field

Mon, 14 Sep 2009 17:53:45 GMT

dude, use purgechar function...1st to remove '-' & then to remove blank spaces.

AW:Re: AW:Re: AW:Creating the same number format in a field

Mon, 14 Sep 2009 17:54:06 GMT

Haha, thast what i'm trying to avoid in order for it to be automated when reloaded if u know what i mean, ok what is the syntax i would use when using mapsubstring, it is a rather large field...

AW:Re: AW:Re: AW:Re: AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 18:17:59 GMT

varun is right, you could also use purgechar, meaning:

PurgeChar('123-456(789)' , '-()' )

would result in

123456789.

Nevertheless, if you like I can also build a demo using MapSubstring.

Rgds,
Joachim

AW:Re: AW:Re: AW:Re: AW:Creating the same number format in a field

Mon, 14 Sep 2009 18:26:51 GMT

So i'd put

purgechar('CellPhoneNo',''-()',')()',(()',' ()',',()'') as CellPhoneNo

To get rid of all the unneccesary dashes and spaces??

Thank you very much

AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 18:36:08 GMT

PurgeChar takes 2 parameters; you need every character only once in the second parameter. Enclosed find an example.

Rgds,
Joachim

AW:Re: AW:Re: AW:Re: AW:Creating the same number format in a field

Mon, 14 Sep 2009 18:39:27 GMT

The thing is there are not only dashes in the wrong format, there are also commas, spaces and brackets which i need to get rid of...

AW:Re: AW:Re: AW:Re: AW:Re: AW:Creating the same number format in a field

biester — Mon, 14 Sep 2009 18:44:41 GMT

Have you looked at my example? Put spaces, commas,brackets,semicolons, whatever you want to 'delete' into the 2nd parameter.

Rgds,
Joachim

AW:Creating the same number format in a field

Mon, 14 Sep 2009 18:56:35 GMT

worked perfectly, thanks alot...