topic Help with Lookup() function in QlikView

Help with Lookup() function

Thu, 11 Feb 2010 18:44:42 GMT

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?

Help with Lookup() function

disqr_rm — Thu, 11 Feb 2010 18:54:59 GMT

You need to use LET and not SET.

Help with Lookup() function

disqr_rm — Thu, 11 Feb 2010 18:56:17 GMT

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

Help with Lookup() function

Thu, 11 Feb 2010 19:25:30 GMT

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:

vLatestPOSDate is already correctly set
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')
I would like to SET the value of vCurrentQuarter. LET will not work on a string value.

Can this be done?

Help with Lookup() function

disqr_rm — Thu, 11 Feb 2010 19:28:27 GMT

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.

Help with Lookup() function

Thu, 11 Feb 2010 19:33:39 GMT

It doesn't work

Help with Lookup() function

Thu, 11 Feb 2010 19:38:01 GMT

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?

Help with Lookup() function

Thu, 11 Feb 2010 19:40:48 GMT

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.

Help with Lookup() function

disqr_rm — Thu, 11 Feb 2010 23:04:21 GMT


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.

Help with Lookup() function

Fri, 12 Feb 2010 18:50:00 GMT

Thank you for your help! It worked 🙂