Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Could you post that script section ?
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);
A quick look gives a small correction:
Applymap('APPLY_CurrencyTable',Division,'EUR') as Currency
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);
hmmm, same error-message. !
how can I add the .qvf-file here ?
Done 😉
Try :
[CurrencyTable]:
Mapping Load * Inline
[Currency,Rate,
EUR,'1,0'
PLN,'4,3'
GBP,'0,7'
CHF,'1,2'
];
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.
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)