Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
in my report I have
temp:
NoConcatenate
LOAD
distinct min (date_sale) as MinDate;
SQL
Select
date_sale
From my.table
let vMinDate = num ('2015-01-01')
but in the report the min date does not start from 2015-01-01 but it starts from 2015-02-01 so it adds one more day, for testing purpose I changed to let vMinDate = num ('2014-31-12'), then it starts from 2015-01-01.
Would you please advice on this and if this is how Qlikview works?
There you have it. In your sample script you don't seem to be using vMinDate for anything else, so try with the following code:
:
let vMinDate = '2015-01-01';
let vMaxDate = num(peek('max_date',-1,'temp'));
let vToday = num(peek('max_date',-1,'temp'));
drop table temp;
calendar_table:
load
date_sale as date_of_sale;
SQL
Select
date_sale
From calendar
Where
date_sale >='$(vMinDate)';
Hi Amir,
Are you trying to use vMinDate for a master calendar? I do not understand the purpose of variable "vMinDate", what is the script after "let vMinDate = num ('2015-01-01')"?
Would be easier if you can share a sample QVW.
Hello Divya,
In my main sql calendar I have all dates, but for this report I want the date to start from 2015-01-01, the use of vMindate is that I will use this variable in different areas in my report.
This is sample of my script
temp:
NoConcatenate
LOAD
distinct min (date_sale) as MinDate;
;
SQL
Select
date_sale
From
calendar
;
let vMinDate = num('2015-01-01');
let vMaxDate = num(peek('max_date',-1,'temp'));
let vToday = num(peek('max_date',-1,'temp'));
drop table temp;
calendar_table:
load
date_sale as date_of_sale;
SQL
Select
date_sale
From calendar
Where
date_sale >=$(vMinDate);
Check the value of variable vMinDate. It's a number (probably 42005), correct?
Is your RDBMS able to understand what this number means? Did you try with a WHERE clause like
:
WHERE date_sale >= '2015-01-01';
Hello Peter,
Yes I tried it with Where clause and it accepts it perfectly.
There you have it. In your sample script you don't seem to be using vMinDate for anything else, so try with the following code:
:
let vMinDate = '2015-01-01';
let vMaxDate = num(peek('max_date',-1,'temp'));
let vToday = num(peek('max_date',-1,'temp'));
drop table temp;
calendar_table:
load
date_sale as date_of_sale;
SQL
Select
date_sale
From calendar
Where
date_sale >='$(vMinDate)';
I agree with Peter, or you may also try
let vMinDate = floor('2015-01-01');
This might work as well.
Hi All,
One more thing to add,
If I my data is coming from SQL load, the date_sale starts from 2015-02-01 adding one more day, but if my data is coming from QVD then the date starts correct from 2015-01-01
qvd script
temp:
NoConcatenate
LOAD
distinct min (date_sale) as MinDate;
FROM
[$(vFileLocation)calendar.qvd](qvd);
;
let vMinDate = num('2015-01-01');
let vMaxDate = num(peek('max_date',-1,'temp'));
let vToday = num(peek('max_date',-1,'temp'));
drop table temp;
calendar_table:
load
date_sale as date_of_sale;
From [$(vFileLocation)calendar.qvd](qvd);
Where
date_sale >=$(vMinDate);
I am really confused here,
That is because date fields in a QVD are QlikView dual fields. Dual values store a string representation (in your case probably like YYYY-MM-DD) together with a binary value à la Excel. The binary value for '2015-01-01 00:00:00' is 42005.
AFAIK there aren't a lot of RDBMS that understand Excel binary date values which you get if you keep using the num() function to drop the text representation. But they all understand dates in Database Format, meaning YYYY-MM-DD.
Thank you Peter and Divya for your help