Normal LOAD translated to SQL SELECT

[Anonymous]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[YoussefBelloum]

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

[Anonymous]

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.