Case Insensitive LookUp

[poorna_76]

Is it possible to do a "Case Insensitive LookUp" from the HashFile?

I have value coming from PrimaryInputRecord as "Single" and in the ReferenceHashfileRecord as "SINGLE".
I should able to say that the record is matched.

How can i do this? Any thoughts?


Thanks in Advance.

[chulett]

Use UpCase on the incoming value before you do the lookup... or you can do it in the actual derivation of the lookup key.

[ameyvaidya]

When loading the hashed file, load them as all upper-case or lower-case characters(see UPCASE() ) in a prior transformer.

Convert the primary lookup to the same case using the key expression:
UPCASE(in.col)

PX has a case-insensitive lookup option. I havent found one yet in server.

[poorna_76]

When loading the hashed file, load them as all upper-case or lower-case characters(see UPCASE() ) in a prior transformer.

Convert the primary lookup to the same case using the key expression:
UPCASE(in.col)

PX has a case-insensitive lookup option. I havent found one yet in server.

Thanks Craig & AmeyVaidya.

But we don't have control over data.


PrimaryInput data can come as one record with LowerCase data and other record as UpperCase data (but definitely not mixed case in a single record)
OR

ReferenceLookup data can come as one record with LowerCase data and other record as UpperCase data (but definitely not mixed case in a single record).

What should we do in that kind of situation.

Thanks in Advance.

[chulett]

Simple, if your lookups can truly be 'case insensitive' then just upcase everything for your keys. Upcase the values in the hashed file. Upcase the values coming in you need to check against the hashed files.

[ray.wurlod]

You DO have control over data - you're the ETL person!!! You can determine what to put in a hashed file - apply an Upcase() function as it goes in. You can determine how to lookup those values - use an Upcase() function in the Key Expression in the Transformer. None of this affects the external data.