topic Scripting task in QlikView

Scripting task

rassanbekov — Fri, 25 Aug 2017 10:33:11 GMT

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,

Re: Scripting task

jonathandienst — Fri, 25 Aug 2017 10:44:58 GMT

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

Amount * Alt(Rate, 0.4)

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.

Re: Scripting task

rassanbekov — Fri, 25 Aug 2017 10:49:20 GMT

the rates per codes are changed monthly...

Re: Scripting task

jonathandienst — Fri, 25 Aug 2017 10:54:00 GMT

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.

Re: Scripting task

effinty2112 — Fri, 25 Aug 2017 10:57:02 GMT

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

Re: Scripting task

jonathandienst — Fri, 25 Aug 2017 10:57:49 GMT

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

Re: Scripting task

vinieme12 — Fri, 25 Aug 2017 11:00:56 GMT

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;

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

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

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;