topic Re: Getting the correct value [Scripting] in QlikView

Getting the correct value [Scripting]

Tue, 26 Feb 2013 07:41:17 GMT

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

currency code

rate

starting date

USD

2/23/2013

USD

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

Re: Getting the correct value [Scripting]

sujeetsingh — Tue, 26 Feb 2013 08:37:45 GMT

Dona please explain the problem in detail.

Re: Getting the correct value [Scripting]

Tue, 26 Feb 2013 08:58:27 GMT

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]

Tue, 26 Feb 2013 23:43:34 GMT

bump!

Re: Getting the correct value [Scripting]

nagaiank — Tue, 26 Feb 2013 23:56:28 GMT

Attached is a solution.

Re: Getting the correct value [Scripting]

Wed, 27 Feb 2013 01:01:59 GMT

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]

rwunderlich — Wed, 27 Feb 2013 03:25:14 GMT

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]

Wed, 27 Feb 2013 03:35:05 GMT

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

currency code

rate

starting date

USD

2/23/2013

USD

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.

Re: Getting the correct value [Scripting]

rwunderlich — Wed, 27 Feb 2013 05:08:23 GMT

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

Re: Getting the correct value [Scripting]

Wed, 27 Feb 2013 08:15:21 GMT

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]

Wed, 27 Feb 2013 08:41:37 GMT

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]

rwunderlich — Wed, 27 Feb 2013 13:16:32 GMT

Can you post some sample data?

-Rob

Re: Getting the correct value [Scripting]

Thu, 28 Feb 2013 02:34:38 GMT

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]

rwunderlich — Thu, 28 Feb 2013 04:36:24 GMT

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]

Thu, 28 Feb 2013 04:56:26 GMT

Alright. I will just concatenate the 2 key fields.

Re: Getting the correct value [Scripting]

rwunderlich — Thu, 28 Feb 2013 05:48:28 GMT

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

-Rob