HF and Seq. file dynamic join

[marc_brown98]

I have a situation where I have a Hash file that I need to use as a lookup for descriptions to insert into a larger sequential file. The problem I am having is that the join to the sequential file depends on a value in the hash file, for example:

key Level Code Desc
______ ____ ________ ________________
52 BAL 2 11010100 PETTY CASH
52 BAL 1 12345600 REVENUE

Seq. File:
key Account_level1 Account_level2
______ _____________ ____________
52 BAL 8080 11010100
52 BAL 12345600 2500

If Level = 1 then I use account_level1 field, if level = 2, use account_level2, etc.

I would like to be able to do this in one pass, but the way I am looking at it would appear that I would have to make a pass through the data for each account_level. Any thoughts on an efficient way to accomplish this?

Thanks
Marc

[roy]

Hi,
you can have multiple lookups for the hash file, each using a different column from your seq file.
in the transformer put the coresponding lookup value depending on the logic you have.
i.e.

Code: Select all

If link.Level = 1 Then lookup1.value Else lookup2.value

you can also use stage variables to make it mor simple:

Code: Select all

SV1 = If link.Level = 1 Then Lookup1.Value Else ""
SV2 = If link.Level = 2 Then Lookup2.Value Else ""
...

SVFinalVal = SV1 : SV2 : ...

Derivation column = SVFinalVal

in the stage variable examples the Else is an empty string.

IHTH,

[ray.wurlod]

Make the hashed file with three key columns (Key, Level, Code).

Perform two (separate) lookups.

The first uses

• inlink.Key
  1
  inlink.Account_Level1

as the three reference key expressions.

The second uses

• inlink.Key
  2
  inlink.Account_Level2

as the three reference key expressions.

One of them should succeed; you can detect which one in the Transformer stage, and output the appropriate description accordingly, for example

Code: Select all

If IsNull(lookup1.Key) Then lookup2.Desc Else lookup1.Desc

[marc_brown98]

ray and roy,
Thanks for the help. That was how I was kind of thinking it through, just wanted to verify.

Marc

[marc_brown98]

Make the hashed file with three key columns (Key, Level, Code).

Perform two (separate) lookups.

The first uses

• inlink.Key
  1
  inlink.Account_Level1

as the three reference key expressions.

The second uses

• inlink.Key
  2
  inlink.Account_Level2

as the three reference key expressions.

One of them should succeed; you can detect which one in the Transformer stage, and output the appropriate description accordingly, for example

Code: Select all

If IsNull(lookup1.Key) Then lookup2.Desc Else lookup1.Desc

Ray,
Would the two lookups be in the same transform?

[chulett]

Would the two lookups be in the same transform?

Yes, it would. Doesn't absolutely need to be, but it is the simplest way to do it.

[ray.wurlod]

Yes.

The derivation expression referring to lookup1 and lookup2 as link names should have been a clue!

[roy]

Hi,
I just wanted to note that sometimes you could end up getting values from both links there for it would be best to use the lookup value coresponding your logic and not the lookup value, as I noted in my post.
IHTH,

[marc_brown98]

Guys,
Thanks for all of the replies, I should have seen how to do it right away, but with a 2 week old baby girl, things are a little foggy now...