15 Replies Latest reply: Feb 28, 2013 12:48 AM by Rob Wunderlich

# Getting the correct value [Scripting]

Hello all,

I would like to describe my tables below:

idcurrency codedate
1USD2/23/2013
2AU2/20/2013

currency coderate
starting date
USD502/23/2013

AU

40

2/23/2013

USD552/24/2013

LINES TABLE:

idamount
11000
2270

Now my problem, the chart that I create is the sum of the amount in the LINES TABLE.

I need to output the computed amount not the raw amount.

Any ideas?

Regards,

Donna

• ###### Re: Getting the correct value [Scripting]

Dona please explain the problem in detail.

• ###### Re: Getting the correct value [Scripting]

I need to know what is the rate of the amounts in my LINES table, depending on what is the date in the HEADER table.

basically the connection goes like this.

I need to look up the rate on the CURRENCY table, the link is CURRENCY CODE. but, the rates that i need to look up depends on the date on the HEADER TABLE.

ex: id 1 currency code is USD, the date is  2/23/2013, so the rate that i have to lookup is  the starting date in the CURRENCY table that is 2/23/2013 (this is from HEADER table) and get the rate. thus linking and computing the id #1 amount is 1000(from LINES table) *50(the rate) = 50000.

I hope you get it. Thanks!

• ###### Re: Getting the correct value [Scripting]

bump!

• ###### Re: Getting the correct value [Scripting]

Attached is a solution.

• ###### Re: Getting the correct value [Scripting]

Hi! Thank you for the reply! Sadly I can't join the HEADER and LINES table because of the actual data that I originally have. HEADER table consists of 500k+ records and its child table is around a million records. If you could provide more idea regarding this. I would glady appreciate it. Thank you!!

• ###### Re: Getting the correct value [Scripting]

Does the starting date in CURRENCY_TABLE get updated every day with a new date or just when the rate changes?

-Rob

• ###### Re: Getting the correct value [Scripting]

It can change daily or weekly.

Sample of its data is like this:

currency coderate
starting date
USD502/23/2013

AU

40

2/23/2013

USD552/24/2013

I can add a starting date of 3/1/2013 for the currency code USD

currency coderate
starting date
USD433/1/2013

If I have a data in the HEADER table and the date is 2/27/13, I have to get the rate of USD in 2/24/13. I have to check if there are equal dates if there is not, I have to find the range between 2 dates depending on the currency.

• ###### Re: Getting the correct value [Scripting]

See attached example. I had to make an adjustment to your sample data. You show a currency=AUS transaction on 2/20 but you don't show a rate for AUS until 2/23. I modified the AUS rate to start on 2/19.

The modeling problem you have is well described by the blog post here

I've created a Qlikview Components subroutine (currently being discussed here) to simplify mapping rates to discrete dates:

http://community.qlik.com/message/315339#315339

I used the discussed Qvc.ExplodeRange in the solution. It implements the techniques described in the first blog post. You could certainly code it by hand as detailed in Henric's post. I just used the Subroutine because it was convienent.

Ther is no need to join the tables. An association between the Header Table date and teh Currency Rate date is enough to calcuate the value as amount * rate.

-Rob

• ###### Re: Getting the correct value [Scripting]

I will definitely check this out. I will come back to you what I have come up. Thanks Rob!

• ###### Re: Getting the correct value [Scripting]

Very helpful answer you gave me Rob! I tried using the first one, works like a charm, but my problem now is connecting it to the HEADER table.

since I need to consider both the CURRENCY CODE and the POSTING DATE [targeted to Starting Date] If you could give me some points. Thank you!

• ###### Re: Getting the correct value [Scripting]

Can you post some sample data?

-Rob

• ###### Re: Getting the correct value [Scripting]

Hi please see attached file. I have already filled up all the dates for the rates in the rate table. Now I need to connect it to the HEADERS table. But if I link it using the currency code, I won't be able to get the appropriate date connected to it.

Hope you get it. Thanks! PSA.

• ###### Re: Getting the correct value [Scripting]

Can you rename the "Posting Date" field to "Date"? That will create a link on Date & Currency Code whilch will link the Header rows to the correct Rate.

-Rob

• ###### Re: Getting the correct value [Scripting]

Alright. I will just concatenate the 2 key fields.

• ###### Re: Getting the correct value [Scripting]

Please mark someting as an answer if it works out for you.

-Rob