Transformation Rules/Logic

iwin · Post by **iwin** » Mon Jul 17, 2006 12:23 pm

Hi,
I am getting 3 columns named A,B,C from Source and i have to do a lookup to a hashed file which has 2 columns named col_ID,col_code where col_ID has values 20,30,40 and col_code has values A,B,C. I have to match such that if the source column name is 'A' and Hashcolumn Col_code value is 'A' then get the ID and so on.
i.e, I have to match the source name with the hashed column value to get the respective id.

Please drop ur suggestion to handle this.

Thanks in advance

kcbland · Post by **kcbland** » Mon Jul 17, 2006 12:33 pm

Sounds like you need multiple reference links to the same hashed file, each one with different literal values in the key expression.

iwin · Post by **iwin** » Mon Jul 17, 2006 12:40 pm

kcbland wrote:Sounds like you need multiple reference links to the same hashed file, each one with different literal values in the key expression.

I have 6 hash files each with 14 columns to match. is there any different logic.

Thanks

kcbland · Post by **kcbland** » Mon Jul 17, 2006 12:58 pm

Your original question stated you needed to align a source column named A to a specific lookup for 3 known values. I now do not understand your followup question because it doesn't correlate to your original.

iwin · Post by **iwin** » Mon Jul 17, 2006 1:03 pm

kcbland wrote:Your original question stated you needed to align a source column named A to a specific lookup for 3 known values. I now do not understand your followup question because it doesn't correlate to your original.

sorry for confusion. Let me come again..

I have a source file with column names A,B,C. And i have a Hashed file with column names Id,col_code, which has values:

ID col_code
20 A
30 B
40 C.

I have to compare the source file column name suppose 'A' with the hashed file column "col_code" value 'A' and get the ID.

Thanks

kcbland · Post by **kcbland** » Mon Jul 17, 2006 1:43 pm

Build another hashed file with col_code as the primary key and ID as the attribute.

iwin · Post by **iwin** » Mon Jul 17, 2006 2:09 pm

kcbland wrote:Build another hashed file with col_code as the primary key and ID as the attribute.

But if i have another hash file, how can i match with the col_code with the source column name. Can please come in detail.

Thanks

kcbland · Post by **kcbland** » Mon Jul 17, 2006 2:24 pm

In the reference link, you need a key expression to match to the primary key. Create three links: link_A, link_B, link_C.

I'm so confused so here's two different answers:

Map column A as the key expression for link_A, B for B, C for C. If the incoming value matches, you'll get your value.

Or

Put literal values in the key expression of "A", "B", and "C" respectively. The exact values will return.

If what you are doing is the second option, you may consider just writing an IF-THEN-ELSE IF statement.

narasimha · Post by **narasimha** » Mon Jul 17, 2006 2:30 pm

As Ken said you have to create a new hashed file.
The key in your hashed file should be "col_code". Link the col_code to the Col A from your source file.
Read the "id" column from the hashed file.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 17, 2006 5:03 pm

To make it clear WHY they advised thus, a hashed file can only be looked up using an exact match on its key column(s).

You are trying to do a "reverse lookup", which is not supported by the Hashed File stage (for the reason above).

You could use a UV stage to perform a reverse lookup against your hashed file, but it would be horribly slow. A reverse-defined hashed file is the best solution.

iwin · Post by **iwin** » Mon Jul 17, 2006 5:26 pm

ray.wurlod wrote:To make it clear WHY they advised thus, a hashed file can only be looked up using an exact match on its key column(s).

You are trying to do a "reverse lookup", which is not supported by the Hashed File stage (for the reason above).

You could use a UV stage to perform a reverse lookup against your hashed file, but it would be horribly slow. A reverse-defined hashed file is the best solution.

Hey Ray,
I dint understand exactly what u said but what i was trying to do is match the incoming "column name" not the column value with TableA on column "code" which has the name of the incoming column.

Here is the sql/rule i have:
select ID from TableA where source column name = col1_code.
( here col1_code one of the column in tableA)

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Jul 17, 2006 7:19 pm

col_code is not the key to the hashed file.

Therefore it can NOT be used for lookup via the Hashed File stage.

You need to create a hashed file in which col_code IS the key column.

iwin · Post by **iwin** » Tue Jul 18, 2006 12:25 pm

Hi All,
Thanks for your suggestion.