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.
bigdataxhi
New Contributor

Convert Julian date

Hi all,

I hope you can help with this quick query.

In my data I have several date fields. The format of the dates is (from what I can see) "Julian" dates set as 7 digits:

YYYY[day of the year in 3 characters]

Samples:

Date_of_Birth

1965166
1970259
1965063

Please can you help with a load script for this Date_of_Birth field, converting the above to "normal" dates in DD/MM/YYYY?

Many thanks!

1 Solution

Accepted Solutions

Re: Convert Julian date

What does the date you are expecting for this? 1965166

May be this?

=TimeStamp( MakeDate( Floor(Date_of_Birth / 1000) ) + 1000 * Frac(Date_of_Birth / 1000) -1 )

Or

=Timestamp(Date_of_Birth /24/60/60s

Life is so rich, and we need to respect to the life !!!
4 Replies

Re: Convert Julian date

What does the date you are expecting for this? 1965166

May be this?

=TimeStamp( MakeDate( Floor(Date_of_Birth / 1000) ) + 1000 * Frac(Date_of_Birth / 1000) -1 )

Or

=Timestamp(Date_of_Birth /24/60/60s

Life is so rich, and we need to respect to the life !!!

Re: Convert Julian date

May be like this?

Table:

LOAD Date_of_Birth,

  Left(Date_of_Birth, 4) as Year,

  Right(Date_of_Birth, 3) as Days;

LOAD * INLINE [

    Date_of_Birth

    1965166

    1970259

    1965063

    1965001

];

Left Join (Table)

LOAD Date_of_Birth,

  IterNo() as Days,

  Date(MakeDate(Year) + IterNo()-1) as Date

Resident Table

While YearStart(MakeDate(Year)) + IterNo() <= YearEnd(MakeDate(Year));

Re: Convert Julian date

Wow this is much simpler

bigdataxhi
New Contributor

Re: Convert Julian date

Perfect, thanks!

Community Browser