Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vinodh_qlikview
New Contributor

Year & Date problem

Hi friends,

i have a problem in date field.

1. i am not able to get year in attached test application.

if any one have soluation please let know.

Regards,

vindh

Tags (3)
9 Replies
vinodh_qlikview
New Contributor

Re: Year & Date problem

Hi all,

any one help me on this

er_mohit
Honored Contributor II

Re: Year & Date problem

could not open your file as far i use personal edition

could you plese share your script or what you exactly want in output

vinodh_qlikview
New Contributor

Re: Year & Date problem

year field is required from date field

MVP
MVP

Re: Year & Date problem

You will need to check the format of your UNIT_DATE, i.e. the exact definition of the DateTime code. I assume it's a UNIX time in millisecond, you can try something like this in your load script or chart:

=Timestamp(UNIT_DATE/(24*60*60*1000)+makedate(1970))

This will return a QV timestamp, formatted with your default Timestamp format code. You'll notice that the returned timestamp is off by 4 hours compared to your UNIT_TIME_STR, probably due to your UNIT_TIMEZONE used.

=Timestamp(UNIT_DATE/(24*60*60*1000)+makedate(1970)-maketime(4))

should correct for this (but you should double check how timezones and day light saving is handled in your source system, there are also QV functions that handle timezone calculations if needed).

If you want to derive year / month / day / dates etc. from the timestamp, just use any QV date / time function:

Year(UNIT_DATE/(24*60*60*1000)+makedate(1970)-maketime(4))

Regards,

Stefan

er_mohit
Honored Contributor II

Re: Year & Date problem

See the attached file

vinodh_qlikview
New Contributor

Re: Year & Date problem

Hi mohit,

I will give u excel data .using that data ,provide me a year field.

in this data UNIT_DATE use as a date field.

Regards,

vindh

er_mohit
Honored Contributor II

Re: Year & Date problem

What type of output you want from that Which Year i have to calculate from .Explain it

MVP
MVP

Re: Year & Date problem

Pls find attached

rustyfishbones
Honored Contributor II

Re: Year & Date problem

You can use the UNIT_TIME_STR to get the Year Date

to get the date

DATE(DATE#(SUBFIELD(UNIT_TIME_STR,'_',1),'YYYYMMDD'),'DD/MM/YYYY') AS Date

to get the Year

YEAR(DATE(DATE#(SUBFIELD(UNIT_TIME_STR,'_',1),'YYYYMMDD'),'DD/MM/YYYY')) AS YEAR

Community Browser