Conditional lookup using hased files

[ekareem]

Hi,
I want to simulate the following logic in 1 transformer:

If data is found after hash1 lookup then
move data from hash1 to output
Else
lookup hash file 2
If data is found in hash2
move data from hash2 to output
Else
move 'not found' to output
End If

Any suggestions? Thanks.

Code: Select all

[loveojha2]

If data is single column then using stage variables you can achieve it (using a single target link).
If it's not then you would need to create three links from the transformer writing to the same target.
First link would be used if NOT(lkp1.NOTFOUND) is ture.
Second link would be used if lkp1.NOTFOUND and NOT(lkp2.NOTFOUND) is true.
Third would be used if lkp1.NOTFOUND and lkp2.NOTFOUND is true.

Hope this will help you.

[kcbland]

There are no conditional lookups.

You could consider using transformers with link collectors to simulate a conditional lookup effect.

Do the first lookup, and based on the result send to two different output links. The first output link acts as if the lookup was found, the second is the lookup not found. Send the first output link to a link collector. From the second output link, go to another transformer and do the second lookup. Send this output to the link collector as well. Now you have the conditional lookup.

[chulett]

Or just have it do both lookups and check them in the proper order. No need for stage variables or extra links, really.

For something as simple as this I don't see a need to go to the trouble of Link Collectors and all that, but it should be kept in mind for the future. Your post has almost the exact logic you would leverage, you just need to move past the perceived need for the lookup to be 'conditional'. The 'condition' is enforced by the order you check each link for success.

Well, unless there is a tremendous amount of overhead involved in on or both of the lookups and they really affect the performance of the job - then consider the methodology Ken mentions.

[loveojha2]

Or just have it do both lookups and check them in the proper order.

The 'condition' is enforced by the order you check each link for success.

Hi Chulett,
ekareem wants to get the data from the two different Hashed lookup files (not from the source). Do you mean that its possible through only one output link? I am not able to understand it. Can you please explain, how would you achieve that?

Thanks

[jenkinsrob]

Just have lookups to both hash files from your single transformer.

Then in the derivation of the output link use the following kind of logic:

If Not(IsNull(Hash1_Lkp)) Then Hash1_lkp Else If Not(IsNull(Hash2_Lkp)) Then Hash2_lkp Else 'Not Found'

[chulett]

Exactly.

[kcbland]

The original subject, "Condition lookup", means that the second lookup is only attempted should the first lookup meet a condition. All lookups in a single tranformer are always attempted. Constraints have no impact whether a reference SQL is fired or a hash reference read is made.

The only method to do a conditional lookup is thru separation of conditions and use of transformers with constraints to divide the rows and send them on for further processing and lookup, with the rows meeting earlier conditions bypassing that subsequent logic. The link collectors are the method for bringing the rows back together.

It's nasty looking, but the only method to make a reference a condition. Personally, I like the cleaner design approach.

[chulett]

The original subject, "Condition lookup", means that the second lookup is only attempted should the first lookup meet a condition. All lookups in a single tranformer are always attempted. Constraints have no impact whether a reference SQL is fired or a hash reference read is made.

Never said that they did. And as you noted, there is no such thing (unless you go 390). Just trying to point out that in this case, as in most cases when someone says they 'need a conditional lookup'... they really don't.

[kcbland]

when someone says they 'need a conditional lookup'... they really don't.

Yup, I agree.

[ray.wurlod]

Conditional lookups are fully supported in the parallel Lookup stage. Who was asleep during class?

A server job solution to implement true conditional lookups would require at least two Transformer stages.

You can do it with one Transformer stage and two reference input links, but both lookups will be performed unconditionally. You then need to create constraint expressions to detect what happened and to control data flows accordingly.

[chulett]

Conditional lookups are fully supported in the parallel Lookup stage. Who was asleep during class?

Hey, we weren't discussing parallel jobs. Besides, it was too flipping cold to fall asleep... well, except maybe from hypothermia.

[ray.wurlod]

We weren't discussing 390 either, but I had to challenge your assertion!

[chulett]

Ah... 390. Was pointing that out because that version has a stage that is actually called the 'Conditional Lookup' stage.