Lookup Not Working

[lukegilligan]

I am having issues getting a lookup working correctly, and I have tried everything I can to get it to work.

I have 2 parts to a customer, a 100000 record and a 3100000, that are linked by the myParent field. If the customer transfers to a different department, the change to a 100000.01 record and a 3100000.01 record. The problem is that the 3100000.01 record in the database refers back to the 100000 record instead of the 100000.01 record (this unfortunately isn't something I can change). What I am hoping to do is us a LOOKUP to lookup the calculated value of the .01 record (3100000.01-3000000) to get me back to the transferred parent (100000.01) and allow me to then use that value to LOOKUP details associated with the transferred customer.

Below is the code of the load script that I am running, where myParent2 is not correctly populating using LOOKUP:

//**************** Load Customer Data ****************

CustomerTemp:

LOAD

code,

name,

    oid AS myCustomer,

    isChildAccount,

    IF(myParent=0,'',

    IF(code LIKE '1*.*','',myParent)) AS myParent;

SQL SELECT

code,

name,

    oid,

    isChildAccount,

    myParent   

FROM LoginSchema.Customer;

LEFT JOIN (CustomerTemp)

LOAD

getCustomer AS myCustomer,

    getJCJob AS myJCJob,

    myHousingCRMOrganisation,

    oid AS myHousingCustomer;   

SQL SELECT

getCustomer,

    getJCJob,

    myHousingCRMOrganisation,

    getCRMOrganisation,

    oid

FROM LoginSchema.HousingCustomer;

TempParentCode:

LOAD

code-3000000 AS TempParentCode,

code

RESIDENT CustomerTemp

WHERE code LIKE '3*.*';

LEFT JOIN (CustomerTemp)

LOAD

code,

LOOKUP('myCustomer','code',TempParentCode,'CustomerTemp')

AS myParent2

RESIDENT TempParentCode;

Attached is also a scrambled QVW

[michael123]

Quick guess ....

Why aren't TempParentCode in quote in your lookup? Checking Help, field value is.

[Peter_Cammaert]

Probably because that's the only parameter for which you want to pass the current value of TempParentCode, not the name of the column 'TempParentCode'. LOOKUP tries to lookup a (possibly variable) value, not the name of a field.

[lukegilligan]

Hi Peter, yes this is correct, TempParentCode is the value I am trying to lookup in the 'code' field.

Does Qlik suffer from the same issue that Excel does when doing a VLOOKUP where if the lookup and comparison fields are different formats, ie Text/numerical it wont return a value?

I am wondering if the TempParentCode is not matching due to it being a calculated field, where as code is pulled straight from the DB?

[lukegilligan]

OK, finally got somewhere....

My theory about the TempParentCode and Code being different data formats looks like it was on the right track.

What I ended up doing was creating a Key from the code&name and using that as my LOOKUP which I presume forced it to be text format, not numerical/text.

Thanks for your help Peter & Michael, its much appreciated.