Fixed value in a lookup

[PhilHibbs]

Is there any way of doing a lookup with a fixed value? I have a data set of price information, and I want to look up the "Buy" price and then the "Sell" price, but I can't specify "Buy" in one lookup link and "Sell" in another.

Would the "right" way to handle this be to split the look up data stream into two Lookup stages with filter conditions controlling which type of price goes into which Lookup stage?

[ray.wurlod]

I'm not sure what you're saying. Surely you're using something like "item number" as the lookup key, so one lookup could return buy price and another lookup could return sell price from the same source data? Of course, two copies of the reference data will be needed in memory as soon as you have two lookups. But each only needs two columns - one has item number and buy price, the other has item number and sell price.

Is this a sparse lookup, perchance?

[PhilHibbs]

Look up data looks like this:

Code: Select all

Date        Time     Type   Price
2010-10-01  00:00    Buy    1.00
2010-10-01  00:00    Sell   1.10
2010-10-01  01:00    Buy    2.00
2010-10-01  01:00    Sell   2.20
2010-10-01  02:00    Buy    3.00
2010-10-01  02:00    Sell   3.30

Stream data has Date, Time, and I want to get the Buy price and Sell price for that hour. So I want to do one look up on Date, Time, "Buy" and one look up on Date, Time, "Sell". I've done it by splitting my look up data into two streams, one for "Buy" and one for "Sell", with each stream going to a different Lookup Stage.

[nitkuar]

You can also use only one Lookup stage with two reference links, having BUY data in one link and SELL in another.

[PhilHibbs]

You can also use only one Lookup stage with two reference links, having BUY data in one link and SELL in another.

I did that early on, but now I always put each lookup in a separate stage, so I can separate out the reject links and get clearer error messages.

[Sreenivasulu]

Hi Phil,
They are different records. You cannot do it in single lookup stage.

You need to group by based on hour and use two lookup stages and a transformer.

Regards
Sreeni

[nitkuar]

I did that early on, but now I always put each lookup in a separate stage, so I can separate out the reject links and get clearer error messages.

that seems logical as well

[chulett]

Can't you include that Type column as a key for the lookup and then hardcode either the 'Buy' or 'Sell' value in the field used for the lookup itself?

[ray.wurlod]

Exactly. You can generate these constants in an upstream Column Generator or Transformer stage.