Page 1 of 1
Fixed value in a lookup
Posted: Mon Oct 04, 2010 3:37 am
by 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?
Posted: Mon Oct 04, 2010 4:02 am
by 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?
Posted: Mon Oct 04, 2010 4:51 am
by 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.
Posted: Mon Oct 04, 2010 5:12 am
by nitkuar
You can also use only one Lookup stage with two reference links, having BUY data in one link and SELL in another.
Posted: Mon Oct 04, 2010 5:44 am
by PhilHibbs
nitkuar wrote: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.
Posted: Mon Oct 04, 2010 5:44 am
by 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
Posted: Mon Oct 04, 2010 6:19 am
by nitkuar
PhilHibbs wrote: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
Posted: Mon Oct 04, 2010 6:54 am
by 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?
Posted: Mon Oct 04, 2010 3:27 pm
by ray.wurlod
Exactly. You can generate these constants in an upstream Column Generator or Transformer stage.