topic Re: Load Date Range via SQL in QlikView

Load Date Range via SQL

Tue, 08 Sep 2015 10:55:28 GMT

Hi,

I've been trying to load the last 30 days worth of data from a database table.

I first tried loading all of the dates, and then in my chart expression using:

DATERUN >= Date(Today()-30)

This does work in a straight table, but not in my chart where the expression is =Asset_Count

I would like to maybe combine the two, perhaps with a where clause, to something like this in my chart:

=Asset_Count where (DATERUN >= Date(Today()-30))

The better option will be for me to instead load the data in the script to the past 30 days.

Although this query works in my DB it does not in QlikView, any ideas why this could be?

;

SQL

SELECT DATERUN

FROM XYZ

Where DATERUN = current date - 30 days;

Any help on either/both of these problems would be much appreciated!

Kind regards,

Tristan

alexandros17 — Tue, 08 Sep 2015 10:59:08 GMT

Try your expression with this new one:

Sum( If(DATERUN_Asset_Status >= Date(Today()-30, 1, 0))

let me know

tresB — Tue, 08 Sep 2015 11:01:03 GMT

Perhaps, something like:

=Count ({<DATERUN {">= Date(Today()-30)"} > } DATERUN_Asset_Status)

If doesn't help, try to share a sample qvw explaining the expected output.

sasiparupudi1 — Tue, 08 Sep 2015 11:24:57 GMT

let vTargetDate=Date(Today(1)-30,'YYYY-MM-DD');

please note that the above formated code must be changed to match your db date format

SQL

SELECT DATERUN

FROM XYZ

Where DATERUN = '$(vTargetDate)';

hth

Sasi

Tue, 08 Sep 2015 11:42:21 GMT

Thanks for the reply!

I've updated the description, I meant 'Count' as a variable as opposed to a function, if that makes a difference?

If not I'll try and update an example.

Tue, 08 Sep 2015 11:43:41 GMT

Thanks for your answer Sasidhar, it didn't throw any errors but I couldn't get it to work either. Could it be that my db is an IBM AS400?