topic Creating a calculation in Script from 2 different Tabs in QlikView

Creating a calculation in Script from 2 different Tabs

nadkalyan — Mon, 11 Jul 2011 00:23:38 GMT

Hi,

I need to create a calculated field in script from 2 different tabs in Edit Script of Qlikview document. In Tab1(let's call it 'Conversion Rate' Tab) we have Conversion Rates column and Tab2 (Lets call it 'Transactions' tab) which has all the transactions. Now i need to add a calculated column in 'Transactions' tab that calculates Base Amount (ActualAmount * ConversionRate) where ActualAmount is from 'Transactions' Tab while ConversionRate in 'Conversion Rate' tab. Any Ideas how can I acheive this?

Creating a calculation in Script from 2 different Tabs

nagaiank — Mon, 11 Jul 2011 01:10:34 GMT

If the Transactions table and [Conversion Rate] table are linked by the currency code field, you can calculate the Base Amount in the charts, using the expression given by you.

If you want to do the calculation in the load script, you may have to bring the ConversionRate to the Transactions table using a left join or mapping load of ConversionRate and then calculate the Base Amount in the script.

Hope this helps.

Creating a calculation in Script from 2 different Tabs

nadkalyan — Mon, 11 Jul 2011 13:13:10 GMT

Thanks krishnamoorthy,

I could get to create a calculated field when creating charts using expression (ActualAmount * ConversionRate), but wanted to see if there is a way i can include this calculation in the Load Script so User no need to compute this logic each time and have him/her pull from Available Fields.

As per your suggestion, I could bring the ConversionRate table to Transactions Tab and script as join and then calculate the BaseAmoun. Can you please explain on second option you mentioned- "mapping load of ConversionRate" . I wanted to try this option also and see which would be best for our needs.

Kalyan

Re: Creating a calculation in Script from 2 different Tabs

nagaiank — Mon, 11 Jul 2011 13:22:48 GMT

Please see if the attached example helps.

The code used in the example for mapping load is:

ConversionRateMapping:

Mapping LOAD * Inline [

Cur, Rate

USD, 1

CAD, 1.1

AUD, 0.75

];

Tnx:

LOAD *,

ApplyMap('ConversionRateMapping',Cur,1) as ConvRate,

ApplyMap('ConversionRateMapping',Cur,1)*Amt as BaseAmount;

LOAD * Inline [

ID, Cur, Amt

1,USD, 10

2,CAD, 10

3,AUD, 10

];

Re: Creating a calculation in Script from 2 different Tabs

Mon, 11 Jul 2011 13:24:10 GMT

Hi Kalyan,

You have two options for Mapping.

Imagine you have only numbers for your items and you also want to see the item-name in the table2

The first option:

table:

MAPPING LOAD

item,

name

FROM dbsource;

table1:

MAP name USING table;

LOAD field1,

field2,

name

FROM dbsource;

=> so you have the item-names as value not the numbers.

Second Option:

table:

MAPPING LOAD

item,

name

FROM dbsource;

table1:
LOAD

field1,

field2,

APPLYMAP('table', item) AS name

FROM dbsource;

=> this option is very useful, if you haven't got the field 'name' in your table.

Important notice: Mapping tables have only two fields.

Regards vicky

Creating a calculation in Script from 2 different Tabs

nadkalyan — Mon, 11 Jul 2011 15:23:40 GMT

ThankYou Krishnamoorthy,

For our requirement bringing in ConversionRate Table and joining with Transaction table seems more logical and simple. It's good to know the mapping logic for future reference.

Kalyan

Creating a calculation in Script from 2 different Tabs

nadkalyan — Mon, 11 Jul 2011 15:24:58 GMT

Thank You Vicky,

As I said our requirment works with join condition for these tables. Appreciate your suggestions.

Kalyan