topic Re: Date filter in the load editor in QlikView

Date filter in the load editor

pascaldijkshoor — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I want to add a date filter for loading data in the load editor. I have tried several where clauses but all didn't work.

The column name is SHIPMENT_DATE and the date format is for example: 1/23/2016 12:00:00 AM

I want to include only the data from 2017 and 2018, could someone help me with this?

Thank you in advance,

Pascal

Re: Date filter in the load editor

Anil_Babu_Samineni — Thu, 01 Feb 2018 16:49:05 GMT

You can define

Load * From Table Where SHIPMENT_DATE >= 'Your Date Format' and SHIPMENT_DATE <= 'Your Date needed';

Re: Date filter in the load editor

buzzy996 — Thu, 01 Feb 2018 16:56:25 GMT

try this way as well,

WHERE SHIPMENT_DATE >='2017/01/01 00:00:00'; (check ur date data format if u have any issue)

Re: Date filter in the load editor

pascaldijkshoor — Fri, 02 Feb 2018 07:45:27 GMT

Thanks for the reply, but both solutions do not work.

Re: Date filter in the load editor

tamilarasu — Fri, 02 Feb 2018 20:44:38 GMT

Hi Pascal,

Try something like,

Data:

Load *

From

Table

Where Year(SHIPMENT_DATE ) >= 2017;

If the above script is not working then SHIPMENT_DATE field should be in text format in your source table. In that case you can try,

Data:

Load *

From

Table

Where Year(Date#(SHIPMENT_DATE,'M/DD/YYYY hh:mm:ss TT')) >= 2017;

Re: Date filter in the load editor

tamilarasu — Fri, 02 Feb 2018 20:46:09 GMT

If you looking for dynamic script, you can try

Let vYear = Year(Addyears(Today(),-1));

Data:

Load *

From

Table

Where Year(SHIPMENT_DATE ) >= $(vYear);

Re: Date filter in the load editor

pascaldijkshoor — Mon, 05 Feb 2018 12:00:33 GMT

I have tried this one, but I get an error that says: "YEAR": invalid identifier

Re: Date filter in the load editor

tamilarasu — Mon, 05 Feb 2018 16:39:20 GMT

Looks like you are fetching data from Oracle. If thst is the case, you can try,

Data:

SQL SELECT *

From

Table

Where Extract( Year From SHIPMENT_DATE ) >= 2017;

Re: Date filter in the load editor

alis2063 — Mon, 05 Feb 2018 18:50:17 GMT

If your are loading data before preceding load as in Sql Source then use Cast fund as below.

Data:

SQL SELECT *

From

Table

where Cast(SHIPMENT_DATE as date) between '01-Jan-2017' and '05-Jan-2018'

Re: Date filter in the load editor

isingh30 — Mon, 05 Feb 2018 20:35:38 GMT

Why are you doing this in the main script when you can do this in the table properties. What table are you using? Please specify the format in the number tab.

Basic Date Format - date#(date,'DD.MM.YYYY')

Thanks.

Re: Date filter in the load editor

pascaldijkshoor — Fri, 09 Feb 2018 11:38:59 GMT

I want to reduce the amount of rows, therefore i want to add the filter in the main script.

Re: Date filter in the load editor

pascaldijkshoor — Fri, 09 Feb 2018 11:39:56 GMT

Unfortunately this did not work as well