topic Re: How to ignore rows after a certain date in QlikView

How to ignore rows after a certain date

JustinDallas — Tue, 16 Dec 2014 20:15:22 GMT

Hello Everyone,

I'm relatively new to Qlikview. One of the issues I am currently stumped by is that we have a load from an excel spreadsheet. Some of the data is irrelevant because it has a "Production Dt" in the 1950s. In SQL, I would just say "SELECT * FROM SomeTable s WHERE YEAR(s.ProductionDt) > YEAR(GETDATE()) -1. However, I have tried to do something similar in Qlikview to no avail. Thus far I've tried

//Set the current date

LET currentDate = Date(Today());

//Load the table with the 'WHERE' clause from the spreadsheet

LamDemandAndCapacity:

LOAD Whse as %WhseId,

[Production Dt], //This column is formatted as mm/d/yyyy

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE YEAR( [Production Dt]) > (YEAR($(currentDate)) - 1);

However, when I run the following code below, the MinDate is always the same day in 1949,

MinMaxDate:

Load Min([Production Dt]) as MinDate, Max([Production Dt]) as MaxDate resident LamDemandAndCapacity;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

LET vDumbDate = Date($(vMinDate));

LET vCurrentYear = Year($(currentDate));\

trace 'Min Date' $(vDumbDate); <----12/29/1949

Any help on this subject is GREATLY appreciated.

Re: How to ignore rows after a certain date

Tue, 16 Dec 2014 20:41:15 GMT

Hi, change just the first line.

Use SET currentDate =Today();

Then expression YEAR($(currentDate)) returns proper result.

Or you could use:

LET currentDate =Today();

And later use YEAR(currentDate), without the $ notation.

Re: How to ignore rows after a certain date

its_anandrjs — Tue, 16 Dec 2014 20:47:54 GMT

Hi,

Try this way while you create the variable from this variable (YEAR($(currentDate)) - 1) and create Year then subtract 1 it will not allow and give random values you have to try this ways Year(currentDate)-1

//Set the current date

LET currentDate = Date(Today());

LET vPreviousYear= Year(currentDate)-1;

//Load the table with the 'WHERE' clause from the spreadsheet

LamDemandAndCapacity:

LOAD Whse as %WhseId,

[Production Dt], //This column is formatted as mm/d/yyyy

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE YEAR( [Production Dt]) > $(vPreviousYear);

Regards

Anand

Re: How to ignore rows after a certain date

JustinDallas — Tue, 16 Dec 2014 20:57:34 GMT

Thanks, looks like I need to take myself to the library and read about the differences between SET and LET, and what the $ actually does. For reference, my ending solution looked like so.

SET currentDate = Today(); <---Big Difference

LamDemandAndCapacity:

LOAD Whse as %WhseId,

[Production Dt],

[Sum of Total Length (ft)]

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE YEAR( [Production Dt]) > (YEAR($(currentDate)) - 1);

MinMaxDate:

Load Min([Production Dt]) as MinDate, Max([Production Dt]) as MaxDate resident LamDemandAndCapacity;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');

LET vDumbDate = Date($(vMinDate));

trace 'Min Date' $(vDumbDate);// <----12/29/1949

Re: How to ignore rows after a certain date

its_anandrjs — Tue, 16 Dec 2014 21:02:32 GMT

Yes you are right when use SET it is static and saved as string and value not calculated and when you use Let it will manipulated the function. Did you check my answer also.

Regards

Anand