topic Date format wierd in QlikView

Date format wierd

userid128223 — Fri, 01 Jun 2012 19:57:51 GMT

my source date format looks like this.

19981203 (YYYYMMDD)

however when i bring that date and try passing thru date function, i get weird numbers.

year(saledate)as year,

I get numbers that is unlike year.

56606

Can someone please explain what is going on.

thanks

Date format wierd

swuehl — Fri, 01 Jun 2012 20:31:16 GMT

QV don't recognize your date format, so it takes the number 19981203 as number of days since 1899-12-30.

And 19981203 is a day in year 56606.

To help QV with your date format, read the date in as

LOAD *, year(saledate) as year;

LOAD

date#(saledate,'YYYYMMDD') as saledate

...

FROM ...;

or change the standard date format at the top of your script accordingly.

Hope this helps,

Stefan

Date format wierd

userid128223 — Fri, 01 Jun 2012 21:07:48 GMT

Thanks

I tried doing something like this but get error. FIELD NOT FOUND "INVOICE DATE"

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE,

month(INVOICE_DATE)as month;

year(INVOICE_DATE) as year;

SQL SELECT "SALEDATE"

FROM.........................................

Re: Date format wierd

swuehl — Fri, 01 Jun 2012 21:23:11 GMT

Yes, in the context of your LOAD, the input field INVOICE_DATE is not known (used in your month and year functions). That's why I used another preceding LOAD (where the field of the first load is known then). Note that even if it's called a preceding load, it's actually a following load (preceding because you write it before / in top of the second load, but the data flows from the SQL to your first LOAD to your second LOAD from bottom to top... Hm, maybe too confusing, right?

So either do

LOAD *,

month(INVOICE_DATE)as month;

year(INVOICE_DATE) as year;

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE;

SQL SELECT "SALEDATE"

FROM.........................................

LOAD "SALEDATE",

Date( Date#( SALEDATE, 'YYYYMMDD'), 'YYYY-MM-DD') as INVOICE_DATE,

month(Date#( SALEDATE, 'YYYYMMDD')) as month;

year(Date#( SALEDATE, 'YYYYMMDD')) as year;

SQL SELECT "SALEDATE"

FROM.........................................

Hope this helps,

Stefan