topic Re: Filter on Julian date in QlikView

Filter on Julian date

qlikviewaf — Sat, 16 Nov 2024 20:56:47 GMT

Hi, i have a sql load on my script and i would like to set a where condition based on the jualian date.

WHERE JulianDate > "Today minus 50 days"

how this can be done with Julian date? this will increase efficiency of the SQL that will load only data of the last 50 days

Thank you

My julian date is as follow:

118001 (is the first day of 2018 - 01/01/2018)

119001 (is the first day of 2019 - 01/01/2019)

tresB — Fri, 03 May 2019 07:59:05 GMT

How would 13th Dec 2019 look like in your julian date format here?

qlikviewaf — Fri, 03 May 2019 08:46:08 GMT

119347

119= 2019

347= is the number of the day

tresB — Fri, 03 May 2019 09:25:20 GMT

Year notation is not very clear yet. Help me formulate a logic for that. How would years 2018, 1999, 2000 be represented then?

qlikviewaf — Fri, 03 May 2019 09:27:10 GMT

the first 3 digit is retrieved in this way: 2019-1900 =119

2020 -1900 = 120

tresB — Fri, 03 May 2019 09:36:58 GMT

Got it. Thanks for the clarifications. You could try like:

Where Num(MakeDate(Left(JulianDate,3)+1900)+right(JulianDate,3)-1) > Num(Today()-50)