Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi comm,
i want to load data from the last 3 days.
Thats why i want to get today - 3 days to get the date x. And with datex i want to load data into Qlik Sense!
FROM Dashboard.dbo."QLIK_TF_SELL";
WHERE Date(Date#(Date_new,'YYYY.MM.DD')) > Date(Date#('x', 'YYYY.MM.DD'))
and x like i said is today -3d!!!
Can i calc this with var in the script?
thanks for help and advices in advance
-Eric
Hi Eric,
please try this,
FROM Dashboard.dbo."QLIK_TF_SELL"
WHERE Cast(CONVERT(VARCHAR(10),Date_new,112) as int) > Cast (Convert(varchar(10),GETDATE()-3,112) as int) ;
This convert will change the date into '20160928' this format
Yes, you can do this by using variable in script.
Convert date in the format as it is in database ,for ex. database has date with format 2016.09.27 then convert it to 'YYYY.MM.DD'
Let var = date(today()-3,'YYYY.MM.DD');
Select *
FROM Dashboard.dbo."QLIK_TF_SELL";
WHERE Date_new > 'var' ;
I hope it is helpful.
Prefer evaluation with the numeric value of the date
Let DateX = today()-3;
FROM Dashboard.dbo."QLIK_TF_SELL";
WHERE NUM(Date_new) > $(DateX) ;
How about just doing this:
FROM Dashboard.dbo."QLIK_TF_SELL";
WHERE Date#(Date_new,'YYYY.MM.DD') > (Today() - 3);
or with variable you might be able to do this
LET vToday3 = Num(Today() - 3);
FROM Dashboard.dbo."QLIK_TF_SELL";
WHERE Date#(Date_new,'YYYY.MM.DD') > $(vToday3);
Thanks for all ur answers,
but i get the following error and i dont know why!
Unknown statement: WHERE
Der Fehler ist hier aufgetreten:
>>>>>>WHERE<<<<<< Date#(Datum,'YYYY.MM.DD') > (Today()-3)
Oh, I totally missed this. I think you are using the where statement in the SQL and Date#() may not be a function in SQL. You will have to use your SQL functions within the SQL SELECT statements. Or you can do this in the LOAD statement.
So i did this in the loading part:
Date(Datum,'YYYY.MM.DD')>'var' as Datum_be,
and this as var above:
Let var = date(today()-3,'YYYY.MM.DD');
it runs perfectly but when i look into the date_be field there are just 0's in it. can u explain to me why?
Well you need to still use a Where statement in your preceding load. Right now all this will give is -1 and 0s
Date(Datum,'YYYY.MM.DD')>'var' as Datum_be,
When true it will give -1 and when not true it will give 0. Are you seeing that?
ok thanks for explaining this.
what database is it? SQL server / Oracle or other?