topic Re: Cleaning Dirty Data in QlikView

Cleaning Dirty Data

Anonymous — Mon, 10 Mar 2014 13:39:44 GMT

Hiya All

I needed help asap please.

Im working on loading data from a Sql Database and storing it as QVD for others to work on.

Im currently stuck and needed help.

The figures being loaded are long and if i load them in Qlikview its similar to that of MS Excel

E.g

20004561+015

The data comes out like this. Is there something i must write in my script to amend this?

So it shows like

20004561235234015?

Any help appreciated.

MK_QSL — Mon, 10 Mar 2014 13:48:11 GMT

Use Below function in your load script..

Evaluate(YourDimension) as YourDimension

UPDATE:

QlikView supports a maximum of 14 digits in your number. If it is longer than that, it will be interpreted a a text.

First, you can use evaluate() to convert your original text into a integer.Then, you can use NUM to format it appropriately

Try Below

num( EVALUATE(YourFieldName),'#0') AS YourFieldName

Anonymous — Mon, 10 Mar 2014 13:57:27 GMT

I tried this but still no joy

Anonymous — Mon, 10 Mar 2014 14:57:41 GMT

Still didn't work

i'm getting this, the numbers are not displayed but you can select them?

rwunderlich — Mon, 10 Mar 2014 15:09:22 GMT

Try wrapping them in a text() function:

text(EntityId) as EntityID

-Rob

bumin — Mon, 10 Mar 2014 15:11:17 GMT

if you try this

num(your_field,'#################') as new_field,

Anonymous — Mon, 10 Mar 2014 15:14:14 GMT

You did it! Bloody Marvelous

Made my day, been on this since Thursday last week