Extract value out of a crosstable in the script

[#1]

Dear Experts,

my English is still not better but I try to explain what I'm working for hours on.

I load rate table

TableRate:

CurrencyCode | Year08 | Year09 | Year10 |

EUR | 1,1 | 1,1 | 1,2 |

USD | 0,9 | 1,0 | 1,1 |

…

as a crosstable.

And I got a data table like this:

TableData:

…|Currency | Date | Value | …

…|EUR | 1.2.09 | 100,00 | …

….

Now I need to calculate in the script by the different rates the Value in the local currency like this

Value * ….(Currency, YEAR(Date), TableRate) >> 100 * 1,1 = 110

So I need a command to select a value out of a Crosstable by given the x and y coordinates

If somebody have a idea to solve my problem I would be happy if he/she shares with me.

THANKS for thinking about!

Regards

John

[disqr_rm]

You need lookup() function. Example enclosed.

[#3 · Accepted solution]

Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4

Hello Rakesh Metha,

thank you very much. I worked on a solution like this for hours and I tried the lookup-function already, but it didn't work.

I'll try to manage it with your example.

Thank you again

Regards from Germany

John

[#4]

Hi Rakesh Mehta,

I tested your solution but it doesn't work because we don't have the different currency in the solution. I can't manage it when the currency is USD in the next line.

Maybe I'm wrong or you have an other idea.

Best regards

John

[disqr_rm]

Oh! I missed that. Sorry John.

See enclosed for your idea. Now, for the large data set, you may want to come upwith a unique key for each repord in Rate table to make the Join sstem friendly.

Hope this helps.

[#6]

Normal 0 21 false false false DE X-NONE X-NONE MicrosoftInternetExplorer4

Hi Rakesh Mehta,

oh that's so simple and genial as well! Great, I can't believe that this problem has cost me one day! Thanks for your help and for saving me another day for nothing.

Best regards

John