DSXchange

Hi,

I need to read a file, populate an array and perform a lookup using that array as part of a routine which will be called per record in a transformer. I haven't used routines before. I would like to know if it is advisable to do file operations in a routine. Also, will the entire routine be executed for every row, specifically if the file operation will be performed for every row or only once to populate array.

Also, will it be better if i do the file read and populate array in before routine and then use the array in the routine for each row. Can that array created in before routine be accessed in the per record routine and if yes, how?

Hi and welcome aboard ,
If your talking Parallel Job why not build a lookup file and perform a lookup on it (instead of array in a routine)?

IHTH,

No, i cannot use a lookup stage. the lookup is like this : first lookup based on 2 fields. If it fails then, default the second feild n do a lookup again on same file. this would mean two lookup file stages and transformers. Thats why was thinking of using a routine. Any ideas pls help...

And having a reject link going to a modify stage to handle_null() and another lookup?
This will result in 2 links that you can eventually load in the logic suiting you.

The specific job design has several variations to implement this idea, simply choose the one that suites you.

IHTH,

I cannot read the premium content, so sorry if this is a repeat.

You can do this in one lookup followed by a transform. Just do the both lookups to the file, so once on 2 keys, once on one key and a default. In the following transformer you put in logic: if lookup1 isnull then lookup2 else lookup1.

Can u please elaborate. I too dont have access to premium content.

Do u mean use two lookup stages or only one in which u do 2 lookups : once using both keys n second time using one key and a default? How is this done? Performing 2 lookups in one lookup stage? Or am i missing somethig here?

I mean one lookup stage that does both lookups.

It may not be directly possible using single lookup stage. But by knowing you exact requirement, like what sort of data is to be looked up, what are the ranges of the discretion of data... the lookup table can be loaded with some manipulation so that a single column can be maintained to lookup. Else as suggested, reject the records and do another lookup and use modify stage to handle_null().

The Lookup stage can perform conditional lookups.

Its done! Implemented by splitting th e cross ref file into 2 : all the rows wid default value for second lookup column put in second file and then pull values from the 2 datasets. One will correspond to actual value and second to default. Chk in following transformer which is the required combination.

Thanks all for ur help