Lookup_IF condition

[pradkumar]

Hi

I am having a staging table which is having amounts in it(different
currency codes). i developed a job already as per my specs.
But now we decided to convert all the amounts which are not in USD to
USD.

We build a lookup table which is having to_currency column and decided to do look up on two columns
and then see if to_currency column is "USD" or not.
If USD then keep the original amount else do the currency conversion.

So is it better to use a lookup in the job design and do lookup on the columns and see for currency code
But I am not understanding how to do "IF" condition after lookup
As far as my knowledge(I am a novice stil) goes we do lookup on a columna nd get the
another column into our target table.

But how to perform this "IF".. I came across If null and If not null
in transformers. But still npt sure how to do this currency conversion check

Could anyone guide me how to do this.

Thanks

[ray.wurlod]

Having performed your lookup, and still in the Transformer stage, if the lookup failed then all columns from the reference input link will have been set to NULL and you can use this fact to determine whether a lookup record was or was not found.

In this case you do not have any record, and must implement a business rule, perhaps to reject the source record (currency type not on file), perhaps to assume that it is USD.

Otherwise the If expression is as you describe.

I would have two outputs, the second to capture source rows with currency codes not on file. Constraint expressions on the two outputs would then be of the form:

Code: Select all

Not(IsNull(RefLink.CurrencyCode))

IsNull(RefLink.CurrencyCode)

The column derivation for amount on the first output link only would be of the form:

Code: Select all

If RefLink.CurrencyCode = "USD" Then InLink.Amount Else InLink.CurrencyAmount * RefLink.ConversionRate

[kimbal]

[quote="pradkumar"]Hi

I am having a staging table which is having amounts in it(different
currency codes). i developed a job already as per my specs.
But now we decided to convert all the amounts which are not in USD to
USD.

We build a lookup table which is having to_currency column and decided to do look up on two columns
and then see if to_currency column is "USD" or not.
If USD then keep the original amount else do the currency conversion.

So is it better to use a lookup in the job design and do lookup on the columns and see for currency code
But I am not understanding how to do "IF" condition after lookup
As far as my knowledge(I am a novice stil) goes we do lookup on a columna nd get the
another column into our target table.

But how to perform this "IF".. I came across If null and If not null
in transformers. But still npt sure how to do this currency conversion check

Could anyone guide me how to do this.

Thanks[/quote]



after performing look up ,before giving a if condtion in transformer,first u fix the raw length =0,because look up failure data are in null.
ifrawlength=0(column name)then (columnname)else (column name)
i think this will solve ur problem.take care. :idea:

[johnthomas]

I think you should try setting the lookup condition , to make sure you do not lookup for amounts which is in USD . Also if you write a if statement try setting @TRUE or @FALSE based on evaluation of the condition

[ray.wurlod]

This is a server job. Conditional lookup is not supported. Nor is there a RawLength() function.

[saikir]

Hey,

If my understanding of your problem is correct, you need to not use a if condition.

You can put a constraint where currency_type=USD then no calculation in the transformer.

If currency_type<>USD then currency*45 or what ever the dollar value is.

[Raghavendra]

You can do your if condition in the following way.
Do a lookup on the table for USD Currency and then get any one not nullable column (say for eample colA)from the lookup to transformer.

If currency is USD then you will find a lookup and ColA will not be nullable.
If currency is not USD then you will find a lookup and ColA will be nullable.

Now in the transformer you can check if colA is not nullable no calculation and if ColA is nullable then do the conversion..

Hope this is helpful

[Nageshsunkoji]

Hi,

Lets take one example for your scenario.

You have 3 fileds 2 from input and 3 from reference.

Input.CountryCode
Input.Amount

Reference.CountryCode
Reference.CurrencyCode
Reference.CurrencyConversionRate.

Your key is CountryCode

Your Logic will be like these :

If Reference.CurrencyCode=(USD)
Then
Input.Amount
Else
perform calculations by using Reference.CurrencyConversionRate
like Input.Amount(*or /) CurrencyConversionRate.

Lets try with above logic.

[pradkumar]

HI

I am struck in the rates table now.
The problem is :
I am doing a lookup on saome keys like year and period.
If the Currency_Code is not USD in the main table and lets say it is EUR,
now I have to do lookup on the rates table for the conversion rate to USD.
Main table:
Curency_Code, Amount, Amountin USD
JPY 12 12*1.23
USD
The lookup table is having following columns:
Year, Period, TO_Currency, Conversion Rate
2006 1 jpy 0.2
2006 1 USD 1.23

So when Currency_Code = USD is failed (main table), it is doing multiplication with JPY conversion rate. Which it shouldn't
My If condition is some what like this

Code: Select all

If Maintable.Currency_Code = 'USD' Then Main.Amount
Else If Lookup.TO_Currency = 'USD' Then Main.Amount*Lookup.Conversion
Else 0

This is not giving proper answers
Plz GIVE ME UR SUGGESTIONS

[chulett]

Here's a couple of suggestions.

1) DON'T SHOUT, it doesn't help and is generally considered rude. People will help when/if and as they can.
2) Spelling is good. There's no such word as 'UR'. Is it really so hard to put the 'Yo' on the front? This isn't your dang cell phone after all.

Now being said, there's not nearly enough information for someone to help you without making more guesses like they've been doing. And there's too much 'like' in your post - be more certain. For example, in your lookup table which columns are the keys? Saying there are 'some keys like year and period' doesn't really help, especially if one assumes the lookup is a hashed file as that isn't unique enough for the data posted. If you are doing the lookup directly against a database table, say so and post the sql you are using in it. Make sure we can tell which columns from the main table you are using in which lookup keys. It would be nice to know what database as well as each has certain... quirks.

And please post your real condition code... not something that is "some what like this". I don't see where you are checking for any kind of a lookup failure.

Then maybe we can get you those 'proper answers'.

[pradkumar]

Thanks for the idea.
Anyway, I figured out the problem.
I am a novice to this forum and I feel sorry if my way of posting questions seems to be rude. I would be good from next time.

[chulett]

Sometimes things seem rude when all you've got is text like this. We tend to read all kinds of things into in, like the fact that all caps is SHOUTING. I we could see each other's faces and body language, some of this wouldn't be a problem.

Speaking of problems, the other thing we'd like people to do is let people know what the solution was when they figure something out. That way, someone who comes searching not only finds the problem but the solution as well! Imagine that!

And please mark the post as 'Resolved'.

Thanks.

[rachitha]

HI

I am struck in the rates table now.
The problem is :
I am doing a lookup on saome keys like year and period.
If the Currency_Code is not USD in the main table and lets say it is EUR,
now I have to do lookup on the rates table for the conversion rate to USD.
Main table:
Curency_Code, Amount, Amountin USD
JPY 12 12*1.23
USD
The lookup table is having following columns:
Year, Period, TO_Currency, Conversion Rate
2006 1 jpy 0.2
2006 1 USD 1.23

So when Currency_Code = USD is failed (main table), it is doing multiplication with JPY conversion rate. Which it shouldn't
My If condition is some what like this

Code: Select all

If Maintable.Currency_Code = 'USD' Then Main.Amount
Else If Lookup.TO_Currency = 'USD' Then Main.Amount*Lookup.Conversion
Else 0

This is not giving proper answers
Plz GIVE ME UR SUGGESTIONS

Hi

Hey in your main table you specified 3 columns:
CurrencyCode , Amount and Amount in USD ..
If your source is providing the amount in USD currency tooo.. then why are you going to Lookup. Directly you can take from input right.

Thank You,
Rachitha.