Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor

Hello, please assst with the following issue:

I have two tables:

First table consists of data on "Code", "Month", "Rate"

The second table has data on "Month", "Code" and "Amount".

As a result, I need to write script to calculate "Rate" x "Amount". the identificatiors are the "Month" and "Code".

The issue is that the first table do not have complete list of codes. Thus if absent "code" and relevant month, I need to multiply "Amount" to 40%.

I tried Mapping function but it does not work with more than three fields.

Kind regards,

1 Solution

Accepted Solutions
Champion III

use applymap() or just Join the two tables.

Table1:

Code,

Month,

Rate

Code&'_'&Month as KEY

From XXX;

MapRate:

Resident  Table1;

Table2:

Code,

Month,

Amount,

Applymap('MapRate',Code&'_'&Month,null()) as Rate,

(Applymap('MapRate',Code&'_'&Month,null()) *Amount) as Evaluated

Code&'_'&Month as KEY

From XXX;

////////////////////////////////////////

OR

///////////////////////////////////////

Table1:

Code,

Month,

Rate

From XXX;

join

T2:

Code,

Month,

Amount

From YYYY

NOCONCATENATE

Main:

Resident Table1;

drop table Table1;

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
6 Replies
Partner - Champion III

Depending on where and how you are using this expression, something like

Amount * Alt(Rate, 0.4)

or

Sum(Amount) * Alt(Rate, 0.4)

Assumes that there at most one Rate for each month/code combination and that there is some sort of relationship between the code and month and the chart dimensions.

Otherwise, post a small example qvw to illustrate the data and your requirements more clearly.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner - Contributor
Author

the rates per codes are changed monthly...

Partner - Champion III

That is to be expected. The amounts and rates are linked with month and code, so no problem there. The amounts are additive but the rates are not, so there should only be one rate per month and code  --  that is what I assumed.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Master

Hi Ruslan,

Sorry to see that this problem is still not resolved. As Jonathan suggests please post sample data or a qvw and I'm sure you can get the advice you need.

Cheers

Andrew

Partner - Champion III

>>... so there should only be one rate per month and code ...

Some month/code combinations have no rates, so the Alt() converts these missing values to 0.4 (40%)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Champion III

use applymap() or just Join the two tables.

Table1:

Code,

Month,

Rate

Code&'_'&Month as KEY

From XXX;

MapRate:

Resident  Table1;

Table2:

Code,

Month,

Amount,

Applymap('MapRate',Code&'_'&Month,null()) as Rate,

(Applymap('MapRate',Code&'_'&Month,null()) *Amount) as Evaluated

Code&'_'&Month as KEY

From XXX;

////////////////////////////////////////

OR

///////////////////////////////////////

Table1:

Code,

Month,

Rate

From XXX;

join

T2:

Code,

Month,

Amount

From YYYY

NOCONCATENATE

Main: