Need Help In Resolving Lookup

[shiva459]

Hi All ,

I have a situation where i need to check the incoming date from source with a table which has upper and lower limits specified for the source columns.Inturn the incoming column values should fall between the upper limit and lower limit.Problem is the user table which has the limits is a stand alone table and in no way can be joined to the source.I have to validate more than 30 such columns coming in from the source.The sample for source and lookup are given below.

Sample Source Record :

I_Num ,sell_rate,buy_rate,payment,net_payment........25 columns.

Stand alone structure :

Outlier OutlierRatioDef LrLimit UrLimit RatioLrLimit RatioUrLimit
Sell_rate sell_rate 1000 6000 5 10
buy_rate buy_rate 2000 5000 NA NA
payment payment 500 800 5 8
.
.
.
40 such records

Now need to see if my source sell rate is between 1000-6000 and also the ratio of sell_rate/abc is between 5-10.

Can any one explain me how to acheive this.I am willing to share the structure of my source table if required.

Any help in this regard will be greatly appreciated.
Thanks in advance.

[Gokul]

Hi,

we were also facing the same Poblem. The Solution we developed was a crude one and Gurus here will tell us whether it is optimal or not?

The Source Structure :
Val id ---------------> Columns
1 1
6 2

The Lookup Table Structure:
Upper Lower Value ---------------> Columns
1 4 A
5 7 B

The Solution we developed:

1> We loaded the Table into hash and added a dummy column LKP which had values '1' for all the records in the hash.
2> We had written a sign(arg1) function which returns 1 if arg1 is postive or 0. It returns 0 for all negative values.

3> The Dummy Column LKP of hash was matched with constraint

Code: Select all

 sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)

The above constraint will return 1 when
1> if the source values matches the Lower limit or is greater than the lower limit and
2> Source value matches the Upper Limit or is less then the Upper limit.

This 1 will match with the Dummy column LKP of the hash ,when the source value lies in the given range.

I know this can also be done using Universe stage.

Thanks

[rumu]

Hi Gokul,
How can u make calculation while doinglookup--like
sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)

When we r makinh lookup we can only access source column not the hash column.

Can u make it a bit clearer.may be i am getting u wrongly

Thanks
rumu

[Gokul]

Hi Rumu,

Code: Select all

sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val) 

will be evaluted first before the Lookup to yield either 1 0r 0.

The Resultant 0 or 1 will be used to Lookup the dummy column LKP in the Hash.

So there shld be no problem in the Lookup.


Thanks,
Gokul

[rumu]

Hi,
U mean to say we will create this dummy lookupfield in first tranformer then in next one we will use this dummy field to make a lookup---is it?

[rumu]

Can u make it a little bit clear?

[Sainath.Srinivasan]

Even though multi-valued may solve some of your problems such as bringing all possible values for a key together in one reference line and in a single fetch, if you need to do range lookups, you may be better-off having them in an uv table and performing an sql lookup.

Shiva459,
Can you provide more information on your requirements as I do not understand where the 'abc' comes into picture and what check / calculation you intend to achieve.

[Gokul]

Hi Rumu

Code: Select all

sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)  
will be evaluted first before the Lookup to yield either 1 0r 0. 

By this i meant that Datastage will evalute the Code (sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)) and then use the result in the lookup. This will be done in the single transformer.

Thanks,
Gokul

[rumu]

Hi Rumu

Code: Select all

sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)  
will be evaluted first before the Lookup to yield either 1 0r 0. 

By this i meant that Datastage will evalute the Code (sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val)) and then use the result in the lookup. This will be done in the single transformer.

Thanks,
Gokul

Gokul,
U mean to say it will be evaluated in a stage varibale then we will use it in lookup?

[Gokul]

Rumu,

It is not in the stage variable.

Code: Select all

sign(source.Val - Hash.Lower) And sign(Hash.Upper - Source.val) 

is written where u usually specify(drag) the source column (field) which will be used in lookup with the corresponding field of the hash.

Datastage will execute the above code to get a value and that value(1 or 0) will be looked up with the Dummy column LKP of the source.

I hope this time i am clear .

[chulett]

In other words, directly in the Key Expression.