topic Re: Trimming unwanted '%' from raw data in QlikView

Trimming unwanted '%' from raw data

shane_spencer — Fri, 05 Sep 2014 13:07:33 GMT

I've got dozens of raw files I load each day (see attachment). I've got a script (attached) that processes these in to QVD format. I've got a field in the raw data called Capacity that shows the figure as a percentage (i.e. 25%). I want to trim the '%' sign from this field whenever it occurs. I don't simply want to trim the last character in case it's not a %, so the script must specifically trim '%' signs only from this field. What's the best way to achieve this?

Re: Trimming unwanted '%' from raw data

Anonymous — Fri, 05 Sep 2014 13:09:06 GMT

Try the PurgeChar() function.

Re: Trimming unwanted '%' from raw data

alexandros17 — Fri, 05 Sep 2014 13:13:34 GMT

The easiest way is, when you load you file (es. gbl05928_filesystem.Sep_04_2014) to do:

Load

...

PurgeChar(Capacity, '%') as capacity

from

...

Let me know

Re: Trimming unwanted '%' from raw data

peterwh — Fri, 05 Sep 2014 13:13:42 GMT

Hi Shane,

I would simpy do an "replace(Capacity, '%', '') AS Capacity". Maybe this helps already

Kind regards

Peter

Re: Trimming unwanted '%' from raw data

shane_spencer — Fri, 05 Sep 2014 13:15:41 GMT

That's perfect - assumed there would be an easy answer, it's just about knowing what that answer is.

PurgeChar(Capacity, '%') as Capacity%

Re: Trimming unwanted '%' from raw data

Anonymous — Fri, 05 Sep 2014 13:19:59 GMT

Yup - It's all there in the QV Desktop Help, but as always the Catch 22 is that it is only easy to find as long as you already know the answer.

Re: Re: Trimming unwanted '%' from raw data

shane_spencer — Fri, 05 Sep 2014 13:29:12 GMT

Thank you Peter Hübschen the "replace" function came in very handy for updating the last 7 months worth of processed data in 1 easy swoop! i.e.

LOAD Date,

Server,

Hour,

Minute,

Main_metric,

replace(sub_Metric, 'Capacity', 'Capacity%') AS sub_Metric ,

replace(value, '%', '') AS value