DSXchange

-- delete --

You can achieve this in a buildop.

Read the reference data in the pre-loop and place it in an array.

Manually or automatically read the input data and for each record write an output everytime you see a 'Y' in the indicator fields (deriving the appropriate values you need from the 4 rows of reference data).

Take a look at the advanced parallel developers guide for an explanation of buildops including the use readRecord(), transferRecord() etc.

You could also get the same result by converting the 'Y's to 1,2,3 or 4 based on which indicator field they are in using a transformer. If the transformer has 4 output links you can create a row of data for each indicator that is 'Y', converting it to 1,2,3 or 4. Run these through a lookup to derive reference values you require.

Thank you. I will give it a try. I will try with transformer first.

Can you please let me know, how can I delcare an array of the reference table. Looks multi - dimensional to me.

Thanks

I think the lookup_string_from_int16() function in a Modify stage would do brilliantly here. Pass all the looked-up columns into a Transformer stage where you can then combine the required ones into the desired row based on If..Then..Else conditions.

Good call Ray. I only watched the video tech tip on Sunday and still I didn't think of using lookup_string_from_int16() function in a Modify stage.