topic Re: DATE FIELD, ERROR IN YEAR in QlikView

DATE FIELD, ERROR IN YEAR

Wed, 25 Nov 2020 16:16:04 GMT

I have a table, one of my dimensions is a date field, but this is show 01/12/2323234 the year is showing 7 digitis. i have funtion date previsouly in the filed. DATE(FIELD, 'DD,MM,YYYY')

i need real information for every year

Thanks.

Re: DATE FIELD, ERROR IN YEAR

sunny_talwar — Wed, 01 Jun 2016 20:08:29 GMT

Can you share a screenshot of what you are seeing?

Re: DATE FIELD, ERROR IN YEAR

Thu, 02 Jun 2016 12:24:29 GMT

hi, sunny, thanks for answer, here the field.

with date funtion

Re: DATE FIELD, ERROR IN YEAR

Chanty4u — Fri, 03 Jun 2016 11:59:46 GMT

try this

Load *,

Date(Date#( DATEFIELD, 'DD,MM,YYYY'),'DD/MM/YYYY') as newDate

Re: DATE FIELD, ERROR IN YEAR

Mon, 06 Jun 2016 16:36:01 GMT

Hi, thanks for answer, with this still happen the same thing!

Re: DATE FIELD, ERROR IN YEAR

MindaugasBacius — Mon, 06 Jun 2016 16:45:55 GMT

I cannot get the pattern.

The number value for today() date is 42527 = 2016.06.06.

I suggest you asking your Business program administrator for Technical reference of the database definitions or Entity diagrams. That may help.

Re: DATE FIELD, ERROR IN YEAR

oscar_ortiz — Mon, 06 Jun 2016 19:19:49 GMT

Question to ask is how the data is stored. The values listed in your second mage do not make sense for a date field. If it truly is a date field what format is the date stored in? As mindaugasbacius‌ suggested talk to your DBA maybe they can shed some light on the field in question.

Good luck

Oscar

Re: DATE FIELD, ERROR IN YEAR

settu_periasamy — Tue, 07 Jun 2016 04:25:22 GMT

Hi Kevin,

Date, seems to be Unix format. May be you can try like...

LOAD
Unix_Date,
Date((Unix_Date/60/60/24)+MakeDate(1970,01,01),'DD.MM.YYYY') as Date1,
date(floor(Unix_Date/ 86400 + 25569)) as Date2,
timestamp(Unix_Date/ 86400 + 25569) as TimeStamp,
time(frac(Unix_Date/ 86400 + 25569)) as Time;
LOAD * INLINE [
    Unix_Date
    1354401000
    1354402800
    1357441200
    1357441200
    1357686000
    1357767000
    1357635600
];

look this thread as well.. Convert unix time stamp to date and time

Re: DATE FIELD, ERROR IN YEAR

Tue, 07 Jun 2016 12:30:20 GMT

settu_periasamy‌ THANK U SO MUCH. is a great solution, and the result was the expected!

Re: DATE FIELD, ERROR IN YEAR

Anonymous — Tue, 27 Feb 2018 22:34:10 GMT

Hi Kevin,

I have the below format Unix dates for which the logic is not working provided by you.

1519346684
1519347743
1519349266
1519349424
1519349948
1519350277
1519350278
1519350289
1519353152
1519353153
1519353263
1519353934
1519355949

Can you please check and tell me the right approach for this.

Thanks,

Rajesh.