Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

How to ignore rows after a certain date

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.

1 Solution

Accepted Solutions
Not applicable

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.

View solution in original post

4 Replies
Not applicable

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.

its_anandrjs

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

JustinDallas
Specialist III
Specialist III
Author

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

its_anandrjs

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