I have some currency exchange rate data, stored in one Excel file per date, with each file containing rates for various currencies. The base currency changes from time to time, e.g. some files show EUR per GBP, USD per GBP, JPY per GBP, and GBP per GBP, whereas others show EUR per USD, USD per USD, JPY per GBP, and GBP per USD.
I have a QlikView application with a table object (in the layout) that converts all the rates to "per GBP" (i.e. sets the base currency as GBP, using set analysis). This is possible since, e.g., if we know that the "EUR per USD" rate is 0.76060 and the "GBP per USD" rate is 0.61554, then we can infer that the "EUR per GBP" rate is 1.23567 (= 0.76060 / 0.61554).
I want to export this table to a CSV for use in another QV application (where I need to apply currency rates to convert prices in various prices all to prices in GBP). In an older version of the application, before the base currency started to change (i.e. it was always "XYZ per GBP" in the source files), I had this working by using a STORE statement in the script, and then every night when there was a server-side reload, I got a nice CSV export which I read into my other QVW.
So I can't just export the table object, as I need it to be exported automatically upon server-side reload (so can't use macros). I think I need to get a script table in the format I want to enable the STORE statement.
In other words, I want to create fields in the script for "Local Cost per GBP" and "GBP Cost per Unit".
I have attached a sample set of data and a QVW showing the desired table format (have a look at the expressions in the 2nd table to see the set analysis I'm using).
My question is: how do I use this set analysis modifier <[LocalCurrency Code]={"GBP"}> within the script to get the fields I want?
Thanks very much for your help!
Sarah
