topic Re: Convert Julian date in QlikView

Convert Julian date

bigdataxhi — Fri, 31 Mar 2017 11:51:40 GMT

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!

Anil_Babu_Samineni — Fri, 31 Mar 2017 12:43:21 GMT

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 )

=Timestamp(Date_of_Birth /24/60/60s

sunny_talwar — Fri, 31 Mar 2017 12:48:13 GMT

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));

sunny_talwar — Fri, 31 Mar 2017 12:50:40 GMT

Wow this is much simpler

bigdataxhi — Fri, 31 Mar 2017 13:25:06 GMT

Perfect, thanks!