topic How to clean data in QlikView

How to clean data

its_anandrjs — Mon, 06 Jun 2011 16:03:39 GMT

Hi All

I have a data in column and it is not in proper order like "-" is added in the numbers i want to remove that and want only numbers.

So how could i get that pls provide help.

Anand

christian77 — Mon, 06 Jun 2011 16:20:35 GMT

Well:

replace(Field,'-','') as Field

If you have something like Barcelona - Real Madrid then use:

subfield(Field,'-',1) as Field1

subfield(Field,'-',2) as Field2

erichshiino — Tue, 07 Jun 2011 01:28:59 GMT

If you just want to remove it,

you can write something like:

purgechar( Field,'-') as Field1

to remove - from your texto

left( Field, len(Field)-1) as Field1

to get all the characters at the left (except the last one)

Regards,

Erich

its_anandrjs — Wed, 08 Jun 2011 05:09:01 GMT

Thanks for that its work.

Regards

Anand

Fri, 10 Jun 2011 10:46:29 GMT

Hi, Do I have to put this in the script file and reload?

erichshiino — Fri, 10 Jun 2011 10:57:42 GMT

Yes, that would be part of your load script,

lets say that you code is:

load a, b, c from abc.xls; (just an example.. you actually need more paramenters, right ?)

you can change it to:

load a, b,

purgechar( c,'-') as c

from abc.xls ;

Fri, 10 Jun 2011 11:48:18 GMT

Thanks Erich