topic Re: Load data from certain date from database in QlikView

Load data from certain date from database

Wed, 25 Nov 2020 16:16:04 GMT

I want to load tables from SQL database from certain date. Since I will run the report once a month, I would like to set a 'StartDate' and a 'EndDate' variable.

For example, for one table, what I do is as follow:

Let StartDate = Date#(5/1/2013,'M/D/YYYY');
Let EndDate = Date#(5/1/2015,'M/D/YYYY');

TableTemp:
Load
[Transaction ID],
[Billed Date],
Payment,
[Paid Date];

SQL select * from Finance.Trasaction
where [Billed Date] >= $(StartDate);

TableTranctions:

Load
[Transaction ID] as ID,
[Billed Date] as BillDate,
Payment,
[Paid Date] as PaidDate
Resident TableTemp
Where [Billed Date] <= $(EndDate);

However, the Qlikview does not compare the date variable, 0 lines is loaded.

Can anyone help me with compare date field with date variable in where clause?

Re: Load data from certain date from database

Clever_Anjos — Fri, 17 Jun 2016 17:45:42 GMT

Why don´t you do this?

TableTemp:
Load
[Transaction ID],
[Billed Date],
Payment,
[Paid Date];
SQL select * from Finance.Trasaction
where [Billed Date] between $(StartDate) and $(EndDate)?

Re: Load data from certain date from database

sunny_talwar — Fri, 17 Jun 2016 17:47:57 GMT

Try this:

LET StartDate = Chr(39) & Date(MakeDate(2013, 5, 1), 'DD-MMM-YYYY') & Chr(39);
LET EndDate = Chr(39) & Date(MakeDate(2015, 5, 1), 'DD-MMM-YYYY') & Chr(39);

Re: Load data from certain date from database

Clever_Anjos — Fri, 17 Jun 2016 17:49:36 GMT

Let StartDate = Date#('5/1/2013','M/D/YYYY'); // you should use quotes too, to avoid the double division 5 / 1 and result / 2013
Let EndDate = Date#('5/1/2015','M/D/YYYY');

Re: Load data from certain date from database

santiago_respane — Fri, 17 Jun 2016 18:01:51 GMT

Have you tried enclosing your date in quotations?

SQL select * from Finance.Trasaction
where [Billed Date] >= '$(StartDate)';

Kind regards,

Re: Load data from certain date from database

Fri, 17 Jun 2016 19:22:10 GMT

This works! Thank you!

So what does Char(39)& , &Char(39) represents here?

Re: Load data from certain date from database

ziadm — Fri, 17 Jun 2016 19:37:56 GMT

SQL use the ISO 8601 Date format by default YYYY-MM-DD

let vStartDate = '2015-06-01';

let vEndDate = '2016-06-17';

SQL SELECT

[Transaction ID],
[Billed Date],
Payment,
[Paid Date];

FROM dbo."DBA"

WHERE Bill_Date >= '$(vStartDate)' and Bill_Date <='$(vEndDate)';

Re: Load data from certain date from database

sunny_talwar — Fri, 17 Jun 2016 21:40:45 GMT

Chr(39) = Single quote, used it to put the date withing the single quote

Re: Load data from certain date from database

sunny_talwar — Fri, 17 Jun 2016 21:41:31 GMT

If you got what you wanted, I would suggest closing the thread by marking correct and helpful responses.

Best,

Sunny