Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
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
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)';
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.