topic Year changes into a numeric field during extraction in QlikView

Year changes into a numeric field during extraction

Wed, 16 May 2012 13:52:37 GMT

Hi all,

i'm connecting to an oracle database.

The years im extracting (eg. 2007, 2008, 2009) are into a column with num(20,0). So no decimals,

Im extracting through an OLE DB connection.

When I get output, the years are represented as a number (eg 2007,00; 2008,00;2009,00).

Is there a setting in the connection or something to let the values just 2007 etc?

Thanks in advance.

Reyman

Year changes into a numeric field during extraction

Jason_Michaelides — Wed, 16 May 2012 15:04:18 GMT

Try any of the following in your load script:

Num(year,'0') AS Year

Floor(Year) AS Year

Round(Year) AS Year

Hopefully one or more of these should work!

Jason

Year changes into a numeric field during extraction

Thu, 31 May 2012 15:19:12 GMT

Normally if it should be 2007.00 and i wanted it to be 2007, I should use num yes.

But now it is 2007 in the database datetype in oracle but in oracle 11 it is internally stored as a timestamp.

I don't want to use and function, but just want that QV reads, hey its a date type, so i need to extract it as a date, not as a timestamp.

Year changes into a numeric field during extraction

Jason_Michaelides — Fri, 01 Jun 2012 09:36:05 GMT

The time portion of a timestamp is the decimal part of the number so Floor(Timestamp) will get you just the date part. Date(Floor(Timestamp),'DD/MM/YYYY'), for example.

If you really don't want to use a function like this you could try changing the SET section at the top of the script:

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET TimestampFormat='DD/MM/YYYY';

Maybe that'll work.

Jason

Year changes into a numeric field during extraction

Jason_Michaelides — Thu, 07 Jun 2012 07:01:09 GMT

Did you get this working?