Help with Lookup() function

[#1]

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?

[disqr_rm]

You need to use LET and not SET.

[disqr_rm]

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')

[#4]

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:

1. vLatestPOSDate is already correctly set
2. I would like to use vLatestPOSDate to perform a Lookup in FiscalCalendarFinal. It should match against FiscCalEndDate and return FiscCalQtr. FiscCalQtr is a string value (e.g., 'Q4')
3. I would like to SET the value of vCurrentQuarter. LET will not work on a string value.

Can this be done?

[disqr_rm]

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.

[#6]

It doesn't work

[#7]

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?

[#8]

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.

[disqr_rm · Accepted solution]

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.

[#10]

Thank you for your help! It worked 🙂