Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having problems with the Lookup function.
In my load script, I've declared a variable that holds a date:
SET vLatestPOSDate = Date(CurrentPOSWk)
Now, I would like to use vLatestPOSDate to perform a table lookup. I have a table named 'FiscalCalendarFinal' that has a field called 'FiscCalEndDate'. So here's my lookup function:
SET vCurrentQtr = Lookup('FiscCalQtr', 'FiscCalEndDate', $(vLatestPOSDate), 'FiscalCalendarFinal');
vCurrentQtr returns no value. Am I using this function correctly? Why isn't it returning a value?
let CurrentPOSWk = today();
SET vLatestPOSDate = Date(CurrentPOSWk);
FiscalCalendarFinal:
LOAD *,
'Q' & ceil(month(FiscCalEndDate)/3) as FiscCalQtr;
LOAD
date(today()+100-recno()) as FiscCalEndDate
AUTOGENERATE 300;
Let vCurrentQtr = Lookup('FiscCalQtr', 'FiscCalEndDate', $(vLatestPOSDate), 'FiscalCalendarFinal')
The same code returns string FiscCalQtr. Lokkup() function will work with any datatype.
May be you want to conver your date fields to num() and then to lookup. Internally, all dates are stored as numeric values, so if you are comparing a string date to numeric date, that wouldn't work.
Still a problem, may be you should post your code here,and someone will get you solution.
You need to use LET and not SET.
Here is an example for you:
let CurrentPOSWk = today();
SET vLatestPOSDate = Date(CurrentPOSWk);
FiscalCalendarFinal:
LOAD
*, QuarterEnd(FiscCalEndDate) as FiscCalQtr;
LOAD
date(today()+100-recno()) as FiscCalEndDate
AUTOGENERATE 300;
Let vCurrentQtr = Lookup('FiscCalQtr', 'FiscCalEndDate', $(vLatestPOSDate), 'FiscalCalendarFinal')
Sorry. Perhaps my original post wasn't clear:
I have a table called FiscalCalendarFinal. I have fields called FiscCalEndDate (primary key) and FiscCalQtr. FiscCalQtr is a string field containing a value (e.g., 'Q4'). FiscCalEndDate is a Date. I would like to use Lookup function, as follows:
Can this be done?
This is exactely what I simulated in my code! It is just giving you end of quarter, but it could be anything. Just copy/paste my code and in a new QV and you will see.
It doesn't work
Sorry, but FiscCalQtr is a STRING...not numeric value. Your example doesn't work in MY script. I get what you are trying to do. Does FiscCalQtr need to be NUMERIC vs. STRING?
Once again, I haven't been clear. LOL. I'm asking if Lookup() returns only a numeric value, or if it can return a string.
let CurrentPOSWk = today();
SET vLatestPOSDate = Date(CurrentPOSWk);
FiscalCalendarFinal:
LOAD *,
'Q' & ceil(month(FiscCalEndDate)/3) as FiscCalQtr;
LOAD
date(today()+100-recno()) as FiscCalEndDate
AUTOGENERATE 300;
Let vCurrentQtr = Lookup('FiscCalQtr', 'FiscCalEndDate', $(vLatestPOSDate), 'FiscalCalendarFinal')
The same code returns string FiscCalQtr. Lokkup() function will work with any datatype.
May be you want to conver your date fields to num() and then to lookup. Internally, all dates are stored as numeric values, so if you are comparing a string date to numeric date, that wouldn't work.
Still a problem, may be you should post your code here,and someone will get you solution.
Thank you for your help! It worked 🙂