topic Re: How to replace characters in all fields with blank/null? in QlikView

How to replace characters in all fields with blank/null?

Sat, 09 May 2015 15:10:11 GMT

Hi All,

I have below sample data, I want to remove these charaters 'ï»¿' to blank/null while loading data. Please advise. Can we do by one script being applied to all columns?

Type	Doc No	Date	Doc Type
A	34765	ï»¿	ï»¿
B	ï»¿	1/24/2015	INV
C	500369	ï»¿	ï»¿
A	ï»¿	ï»¿	ï»¿
B	ï»¿	ï»¿	ï»¿
C	ï»¿	ï»¿	ï»¿

Thanks,

SAK

Re: How to replace characters in all fields with blank/null?

sunny_talwar — Sat, 09 May 2015 15:16:32 GMT

This should work:

LOAD PurgeChar(Type, 'ï»¿') as Type,

PurgeChar([Doc No], 'ï»¿') as [Doc No],

PurgeChar(Date, 'ï»¿') as Date,

PurgeChar([Doc Type], 'ï»¿') as [Doc Type]

Resident/FROM

HTH

Best,

Sunny

Re: How to replace characters in all fields with blank/null?

sunny_talwar — Sat, 09 May 2015 15:19:54 GMT

Tried it with your sample data:

Table:

LOAD PurgeChar(Type, 'ï»¿') as Type,

PurgeChar([Doc No], 'ï»¿') as [Doc No],

PurgeChar(Date, 'ï»¿') as Date,

PurgeChar([Doc Type], 'ï»¿') as [Doc Type];

LOAD Type,

[Doc No],

Date,

[Doc Type]

FROM

[https://community.qlik.com/thread/163361]

(html, codepage is 1252, embedded labels, table is @1);

Output looks like this:

Re: How to replace characters in all fields with blank/null?

petter — Sat, 09 May 2015 15:20:09 GMT

How did you load this data? It looks like you specified the wrong character set or code page... Maybe if you changed that you wouldn't get the strange characters...

Re: How to replace characters in all fields with blank/null?

Sat, 09 May 2015 19:24:26 GMT

Yep, you are right.

First I got data downloaded to excel from HTML webpage and hence got these characters.

But then I directly loaded into qlikview, so now it's all fine

Re: How to replace characters in all fields with blank/null?

Sat, 09 May 2015 19:27:04 GMT

The labels are now coming in first row of data loaded even though I have made it embedded labels.

IS there any way to add header lines in embedded labels while loading data from HTML wewebpage?