Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

How to clean data

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

1 Solution

Accepted Solutions
erich_shiino
Honored Contributor

How to clean data

If you just want to remove it,

you can write something like:

purgechar( Field,'-') as Field1

to remove - from your texto

or

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

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

Regards,

Erich

6 Replies
christian77
Valued Contributor

How to clean data

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

erich_shiino
Honored Contributor

How to clean data

If you just want to remove it,

you can write something like:

purgechar( Field,'-') as Field1

to remove - from your texto

or

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

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

Regards,

Erich

How to clean data

Thanks for that its work.

Regards

Anand

Not applicable

How to clean data

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

erich_shiino
Honored Contributor

How to clean data

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 ;

Not applicable

How to clean data

Thanks Erich

Community Browser