topic Re: Format of a field value in QlikView

Format of a field value

Wed, 18 Jun 2014 16:03:26 GMT

I am loading a field called "Group" directly from a website. One of the values is "Open (20 – 39 yrs)", but Qlikview is interperting the equals sign ("=") to three very weird characters (a€“). How do I correct this?

Re: Format of a field value

alexandros17 — Wed, 18 Jun 2014 16:07:20 GMT

Where is the equal sign in the string Open (20 – 39 yrs) ?

Re: Format of a field value

swuehl — Wed, 18 Jun 2014 16:13:05 GMT

I think you need to specify the correct character set in your load. Could you post your current script snippet?

Re: Format of a field value

Wed, 18 Jun 2014 16:14:29 GMT

Sorry, I meant dash.

Alex

Sent from my iPhone

Re: Format of a field value

Wed, 18 Jun 2014 16:27:24 GMT

CrossTable(Year, GroupPercent)

LOAD [Â ] as Group,

     [1980],

     [1995],

     [2000],

     [2005],

     [2010],

     [2011],

     [2012],

     [2013]

Re: Format of a field value

cspencer3 — Wed, 18 Jun 2014 16:40:05 GMT

Could you load the field name using a string replace function, and replace the dash with a new character or a character function chr(45) ; character function of 45?

Re: Format of a field value

its_anandrjs — Wed, 18 Jun 2014 17:25:41 GMT

You can use PurgeChar function to purge the values which is specifying by you if always get same values like (a€“)

Load

PurgeChar(Str,'a€“') as

From Location;

Eg:-

LOAD PurgeChar(Str,'a€“') as NewStr,Str;

LOAD * Inline

[

Str

a€“Open (20 – 39 yrs)a€“

];

Re: Format of a field value

Wed, 18 Jun 2014 17:43:17 GMT

But I need the dash.

Alex

Sent from my iPhone

Re: Format of a field value

its_anandrjs — Wed, 18 Jun 2014 17:52:56 GMT

Then try with this

Load

Replace(Str, 'a€“' , '-') as Str2

From Location;

Re: Format of a field value

Wed, 18 Jun 2014 18:01:54 GMT

Ahh! Perfect! Thanks! Will mark it later!

Alex

Sent from my iPhone

Re: Format of a field value

swuehl — Wed, 18 Jun 2014 18:06:08 GMT

I would personally prefer fixing the issue at the source, not the symptoms.

When I asked for the script snippet, I was actually asking for the complete LOAD statement, most interested in the part where you specifiy the source and the source format. I still assume specifying the source correctly would fix your issue with the dash (and potentially also other issues you just haven't noticed yet with the broken character set).

Re: Format of a field value

Wed, 18 Jun 2014 18:38:30 GMT

CrossTable(Year, GroupPercent)

LOAD [Â ] as Group,

     [1980],

     [1995],

     [2000],

     [2005],

     [2010],

     [2011],

     [2012],

     [2013]

FROM

[http://www.runningusa.org/marathon-report-2014?returnTo=annual-reports]

(html, codepage is 1252, embedded labels, table is @1, filters(

Re: Format of a field value

Wed, 18 Jun 2014 18:45:25 GMT

Changing it to UTF8 Character set fixed it perfectly! Thanks!