Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Getting the correct value [Scripting]

Hello all,

I would like to describe my tables below:

HEADER TABLE:

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

CURRENCY TABLE: (everyday it updates)

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

1 Solution

Accepted Solutions

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

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

15 Replies
sujeetsingh
Honored Contributor III

Re: Getting the correct value [Scripting]

Dona please explain the problem in detail.

Not applicable

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!

Not applicable

Re: Getting the correct value [Scripting]

bump!

nagaiank
Valued Contributor III

Re: Getting the correct value [Scripting]

Attached is a solution.

Not applicable

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

Not applicable

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

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

Not applicable

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!

Community Browser