5 Replies Latest reply: Feb 28, 2010 4:02 PM by John De Loach

# Extract value out of a crosstable in the script

Dear Experts,

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

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.

Regards

John

• ###### Extract value out of a crosstable in the script

You need lookup() function. Example enclosed.

• ###### AW:Re: Extract value out of a crosstable in the script

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

• ###### AW:Re: Extract value out of a crosstable in the script

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

• ###### AW:Re: Extract value out of a crosstable in the script

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.

• ###### AW:Re: AW:Re: Extract value out of a crosstable in the script

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