Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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
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
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