Creating a calculation in Script from 2 different Tabs

[nadkalyan]

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?

[nagaiank]

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.

[nadkalyan]

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

[nagaiank]

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

];

[#5]

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

[nadkalyan]

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

[nadkalyan]

Thank You Vicky,

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

Kalyan