Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

Convert Julian Date to regular date in data load script SQL

I need to query an ODBC data source directly and grab data that is for the current date.  Unfortunately, the dates are in Julian format, such as today's date would be 18309, but in JD Edwards there's also a leading digit for the century, so we're looking at a JDE Julian Date of 118309.

I'm looking for a way to convert that to a calendar date in the SQL load script in the Qlik Sense data load editor. 

Currently, SQL in the load script looks like this:

LOAD PDITM,

PDPDS2,

PDPDS5,

PDPQOR;

[F4311]:

SELECT "PDITM",

"PDPDS2",

"PDPDS5",

"PDPQOR"

FROM "TNA73DTA"."F4311"

WHERE "PDDCTO" = 'OK'

AND "PDPDS1" = 'CON'

AND "PDLTTR" BETWEEN '219' AND '401'

AND "PDDRQJ" = '118309';


Ideally, I'd like that last AND to look something like this:

AND DATE("PDDRQJ") = CURDATE()

or the equivalent in Qlik-compatible SQL.

Any ideas?

Thanks.

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

To convert from a Qlik date (Gregorian) to a Julian date JDE you can use this expression:

=(Year(QlikDate)-2000+100)*1000+Num(QlikDate)-MakeDate(Year(QlikDate),1,1)+1

For example:

QlikDate = MakeDate(2018,11,5);

JDEdate = (Year(QlikDate)-2000+100)*1000+Num(QlikDate)-MakeDate(Year(QlikDate),1,1)+1;

LOAD PDITM,

PDPDS2,

PDPDS5,

PDPQOR;

[F4311]:

SELECT "PDITM",

"PDPDS2",

"PDPDS5",

"PDPQOR"

FROM "TNA73DTA"."F4311"

WHERE "PDDCTO" = 'OK'

AND "PDPDS1" = 'CON'

AND "PDLTTR" BETWEEN '219' AND '401'

AND "PDDRQJ" = '$(JDEdate)';


QlikDate=;JDEdate=;

View solution in original post

4 Replies
petter
Partner - Champion III
Partner - Champion III

Actually there is no such thing as "Qlik-compatible SQL".

The entire SELECT statement is sent as-is directly to the connector which most often is an ODBC connector. So it is the ODBC connector and the database that the specific ODBC connector connects to that defines what a compatible SQL is.

The only thing Qlik is concerned with is the columns and rows that are returned by the connector (and that the columnnames are unique - which they might not be since a SQL select might have duplicate names without a problem).

However you can use $-sign expansion with variables from Qlik to insert into the select statement.

petter
Partner - Champion III
Partner - Champion III

To convert from a Qlik date (Gregorian) to a Julian date JDE you can use this expression:

=(Year(QlikDate)-2000+100)*1000+Num(QlikDate)-MakeDate(Year(QlikDate),1,1)+1

For example:

QlikDate = MakeDate(2018,11,5);

JDEdate = (Year(QlikDate)-2000+100)*1000+Num(QlikDate)-MakeDate(Year(QlikDate),1,1)+1;

LOAD PDITM,

PDPDS2,

PDPDS5,

PDPQOR;

[F4311]:

SELECT "PDITM",

"PDPDS2",

"PDPDS5",

"PDPQOR"

FROM "TNA73DTA"."F4311"

WHERE "PDDCTO" = 'OK'

AND "PDPDS1" = 'CON'

AND "PDLTTR" BETWEEN '219' AND '401'

AND "PDDRQJ" = '$(JDEdate)';


QlikDate=;JDEdate=;

mikegrattan
Creator III
Creator III
Author

Just had to make one edit and it worked perfectly.  Instead of using MakeDate() in the QlikDate variable I used Date(). I understand why your example used MakeDate, as you were putting together three different components of a date, but I needed today's date.  This works:

QlikDate = Date(Today());

JDEdate = (Year(QlikDate)-2000+100)*1000+Num(QlikDate)-MakeDate(Year(QlikDate),1,1)+1;

LIB CONNECT TO 'Iceberg (tasp-qlik_administrator)';

LOAD PDITM,

PDPDS2,

PDPDS5,

PDPQOR;

[F4311]:

SELECT "PDITM",

"PDPDS2",

"PDPDS5",

"PDPQOR"

FROM "TNA73DTA"."F4311"

WHERE "PDDCTO" = 'OK'

AND "PDPDS1" = 'CON'

AND "PDLTTR" BETWEEN '219' AND '401'

AND "PDDRQJ" = '$(JDEdate)'

;

Thank you for your great response!

petter
Partner - Champion III
Partner - Champion III

You're welcome