Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

DATE FIELD, ERROR IN YEAR

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.

1 Solution

Accepted Solutions
Highlighted

Re: DATE FIELD, ERROR IN YEAR

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

];

Capture.JPG

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

View solution in original post

9 Replies
Highlighted

Re: DATE FIELD, ERROR IN YEAR

Can you share a screenshot of what you are seeing?

Highlighted
Not applicable

Re: DATE FIELD, ERROR IN YEAR

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

with date funtion

1.jpg

Highlighted
Chanty4u
Esteemed Contributor III

Re: DATE FIELD, ERROR IN YEAR

try this

Load *,

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

Highlighted
Not applicable

Re: DATE FIELD, ERROR IN YEAR

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

Highlighted
MindaugasBacius
Valued Contributor III

Re: DATE FIELD, ERROR IN YEAR

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.

Highlighted
Partner
Partner

Re: DATE FIELD, ERROR IN YEAR

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

Highlighted

Re: DATE FIELD, ERROR IN YEAR

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

];

Capture.JPG

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

View solution in original post

Highlighted
Not applicable

Re: DATE FIELD, ERROR IN YEAR

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

Highlighted
brajesh5050
New Contributor

Re: DATE FIELD, ERROR IN YEAR

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.