Currency-Conversion within load-script

[reinholdgraf]

Hi.

I want to convert e.g. GBP-Values into €-Values within the load-script.

I first load a currency-table (currency and conversion-rate) (e.g. GBP, 0.7)

I secondy allocate the currency to the company (e.g. 100, GBP)

In the script for loading sales-figures, I now need to

convert e.g. GBP-Sales into €-Sales.

How can I use the (already loaded) currency-table within the load-script ?

Actually, I get an error saying, that the fields of the currency-table are not known (Field not found).

What am I doing wrong ?

[tresesco]

Could you post that script section ?

[Kushal_Chawda]

see this

Currency Conversion, to multiple choices | Qlik Community

Currency Conversion

[reinholdgraf]

Yes.

[CurrencyTable]:

Load * Inline

  [Currency,Rate,

  EUR,'1,0'

  PLN,'4,3'

      GBP,'0,7'

      CHF,'1,2'

  ];

[APPLY_Currency]:

  mapping

    load * Inline

        [Division,Currency,

            400,PLN

            700,GBP

            800,CHF

            ];

[Division]:

LOAD

  [Division],

    Applymap('APPLY_CurrencyTable',Division,'EUR') as Currency

FROM [lib://produktiv/Currency.xlsx]

(ooxml, embedded labels, table is Div);

[Sales]:

LOAD

  [Division],

  [Sales],

    [Sales]/Currency as [Sales-€]

FROM [lib://produktiv/Currency.xlsx]

(ooxml, embedded labels, table is Sales);

[tresesco]

A quick look gives a small correction:

Applymap('APPLY_CurrencyTable',Division,'EUR') as Currency

[tresesco]

And probably one more:

[Sales]:

LOAD

  [Division],

  [Sales],

    [Sales]/Num(ApplyMap('CurrencyTable', Currency)) as [Sales-€]

FROM [lib://produktiv/Currency.xlsx]

(ooxml, embedded labels, table is Sales);

[reinholdgraf]

hmmm, same error-message. !

how can I add the .qvf-file here ?

Done 😉

[tresesco]

Try :

[CurrencyTable]:

Mapping Load * Inline

  [Currency,Rate,

  EUR,'1,0'

  PLN,'4,3'

      GBP,'0,7'

      CHF,'1,2'

  ];

[reinholdgraf]

No.

I want to have the CurrencyTable as a separate table for further reportings, etc.

The problem is, that the script doesn't recognize the field "currency", which is built just before load the sales, where the error-message occurs.

[#10]

Load your CurrencyTable, then create a MAPPING LOAD [Currency],[Rate] RESIDENT CurrencyTable;

means you have the CurrencyTable available afterwards (although you may want to change the fields names, if you don't want Qlik to create synthetic joins)