Two lookups on same column..

[Srik]

Can any one suggest if the below requirement is fissable.

Table AB is formed by A union B

In AB table their is column <ID> which has 2 different values these values can be identified by <SOURCE> cloumn in table AB.

i need do lookups on <ID> column using lookup A & lookup B as this column is formed with two different values.

i would like to know whether this is possible in DS.

And i would also like to know can we do lookup by condition.
(Lookup A) on <ID> using <Source> as condition.

[MFGF]

Hi,

My advice would be to stick with a single lookup.  Instead of trying to validate your one column against two separate lookups, bring the ID values from both datasets into the same lookup (using two data sources if necessary) and validate your UNIONed table IDs against this.

If you must validate the two sets of IDs separately, then you will probably find it easiest to create two fact builds, each validating against a single lookup, and code the data source for each fact build to contain a WHERE clause to bring in just the relevant rows to validate against the lookup in that build.

The final option, if you really must do the validations all in one fact build and do them separately, is to code a transformation model derivation using an if -- then -- else construct based on the two types of ID value in your source.  Each block of the derivation would need to call a lookup() function to validate against the relevant table for the given type of ID.  The big drawback of this method is that the validations are not done in memory as with a regular lookup object, so performance would suffer.

Hope that helps,

MF.

[rmcleod]

Another way to do it is to split the one datastream column into two transformation columns. Put one lookup on the higher column, the other on the lower. Make them private.
Build two derivations for the lookup results to land underneath and initialise.
Build a third derivation that tests if the first one is null, then if it is, use the second derivation result.
This way you can use two lookups on the same column and its all done in memory.