Scripting task

[rassanbekov]

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.

If possible please share your scripts.

Kind regards,

[jonathandienst]

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.

[rassanbekov]

the rates per codes are changed monthly...

[jonathandienst]

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.

[effinty2112]

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

[jonathandienst]

>>... 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%)

[vinieme12 · Accepted solution]

use applymap() or just Join the two tables.

Table1:

LOAD

Code,

Month,

Rate

Code&'_'&Month as KEY

From XXX;

MapRate:

LOAD Distinct KEY,Amount

Resident  Table1;

Table2:

LOAD

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:

LOAD

Code,

Month,

Rate

From XXX;

join

T2:

LOAD

Code,

Month,

Amount

From YYYY

NOCONCATENATE

Main:

LOAD * , RATE*AMOUNT

Resident Table1;

drop table Table1;