Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

calc date for data load[load editor]

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

1 Solution

Accepted Solutions
lakshmikandh
Specialist II
Specialist II

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

View solution in original post

19 Replies
shraddha_g
Partner
Partner

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.

vinieme12
Champion III
Champion III

Prefer evaluation with the numeric value of the date

Let DateX = today()-3;


FROM Dashboard.dbo."QLIK_TF_SELL";

WHERE NUM(Date_new) > $(DateX) ;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

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


Not applicable
Author

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)

sunny_talwar

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.

Not applicable
Author

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?

sunny_talwar

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?

Not applicable
Author

ok thanks for explaining this.

vinieme12
Champion III
Champion III

what database is it? SQL server / Oracle or other?

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.