topic Re: Convert Julian Date to regular date in data load script SQL in Connectivity & Data Prep

Convert Julian Date to regular date in data load script SQL

mikegrattan — Mon, 05 Nov 2018 23:14:28 GMT

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.

Re: Convert Julian Date to regular date in data load script SQL

petter — Tue, 06 Nov 2018 03:56:58 GMT

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.

Re: Convert Julian Date to regular date in data load script SQL

petter — Tue, 06 Nov 2018 04:13:18 GMT

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

Re: Convert Julian Date to regular date in data load script SQL

mikegrattan — Tue, 06 Nov 2018 15:00:12 GMT

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!

Re: Convert Julian Date to regular date in data load script SQL

petter — Tue, 06 Nov 2018 17:01:49 GMT

You're welcome