topic Re: Normal LOAD translated to SQL SELECT in QlikView

Normal LOAD translated to SQL SELECT

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi, I have translated my normal load script into SQL Select as I am now connecting to ODBC. Everything works fine except when I incorporate the Today() in my WHERE, suddenly no data is getting loaded. No errors. But no data is showing up. Not sure what is wrong.

ORIGINAL SCRIPT:

LOAD reference_id as ID,

Sum(costs) as TotalCosts

FROM

[Desktop\Sample Excel.xlsx]

(ooxml, embedded labels, table is [Sample Excel]) WHERE code='ABC' and start_date<=Today() and end_date>=Today() Group by reference_id;

REVISED SCRIPT:

Let vToday = Today();

LOAD reference_id as ID,

Sum(costs) as TotalCosts

Group reference_id

;

SQL SELECT

reference_id,

costs

FROM

"Table from MS Access"

WHERE

code='ABC' and start_date<=$(vToday) and end_date>=$(vToday);

Re: Normal LOAD translated to SQL SELECT

Miguel_Angel_Baeyens — Tue, 13 Feb 2018 10:36:01 GMT

If start_date field in the database is not a number in the same format that QlikView stores dates (e.g.: 41234.0987) then I would suggest instead:

WHERE

code='ABC' and start_date<='$(vToday)' and end_date>='$(vToday)';

Or double quotes depending on your driver and DBMS

Re: Normal LOAD translated to SQL SELECT

YoussefBelloum — Tue, 13 Feb 2018 10:39:53 GMT

Hi,

try to verify that:

the format of the "DateFormat" variable on your qlikview app is the same that the date format on your sql database.

Also try with this on the where:

WHERE

code='ABC' and start_date<='$(vToday)' and end_date>='$(vToday)';

Re: Normal LOAD translated to SQL SELECT

Anonymous — Tue, 13 Feb 2018 10:42:39 GMT

Thanks. I realize from reading your response what was wrong with it. The dates in my database apparently was in number format. I changed the variable to:

Let vToday = num(Today());

And that solved it.