Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Scripting task

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,

1 Solution

Accepted Solutions
Highlighted
Champion II
Champion II

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;

View solution in original post

6 Replies
Highlighted

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
Highlighted
Partner
Partner

the rates per codes are changed monthly...

Highlighted

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
Highlighted
Master
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

Highlighted

>>... 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
Highlighted
Champion II
Champion II

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;

View solution in original post