Getting the correct value [Scripting]

[#1]

Hello all,

I would like to describe my tables below:

HEADER TABLE:

id	currency code	date
1	USD	2/23/2013
2	AU	2/20/2013

CURRENCY TABLE: (everyday it updates)

currency code	rate	starting date
USD	50	2/23/2013
AU	40	2/23/2013
USD	55	2/24/2013

LINES TABLE:

id	amount
1	1000
2	270

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

[sujeetsingh]

Dona please explain the problem in detail.

[#3]

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!

[#4]

bump!

[nagaiank]

Attached is a solution.

[#6]

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!!

[rwunderlich]

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

-Rob

[#8]

It can change daily or weekly.

Sample of its data is like this:

currency code	rate	starting date
USD	50	2/23/2013
AU	40	2/23/2013
USD	55	2/24/2013

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

currency code	rate	starting date
USD	43	3/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.

[rwunderlich · Accepted solution]

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

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field.

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

[#10]

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