Dual-base Currency

Case SummaryApp Building

Dual-base Currency (1d)

After I complete the implementation of Foreign Currency Transaction (1c), multiplying exchange rate for every transaction, not knowing how to maintain local transaction currency, local base currency and reporting currency for consolidation e.g. HKD at the same time.


Assuming that you understand the previous solution “Operation in foreign currency (1c)”, the detailed steps are not repeated here.

There are no restrictions on the number of the base currencies. However, you must assign a different currency name, for example, Tran Currency and Local Currency. In addition, the previous example shows how to use the JoinTable, Multiply, and Round functions; you need to configure it twice for the ETL. In addition, you need to create two different master tables of exchange rates as follows:-

  • JoinTable(TranExRate): Convert Foreign Currency Transaction in Foreign Currency to Local Branch Base Currency e.g. CNY
  • JoinTable(ReportExRate): Convert Local Branch Transaction in Local Base Currency to Group Base Currency e.g. HKD

You see in the JoinTable worksheet, currency are USD-CNY and CNY-HKD. This should avoid confusion for the two exchange rate tables. In fact, you can input a currency without the suffixes “-CNY” and “-HKD”.

Sample Data

An assumption is made about the source data – the presence of the columns “Ledger”, “Data”, “Customer”, “Doc Number”, “Product”, “Quantity”, “Unit Price”,“Tran Currency”, “Tran Amount”, “Local Currency”. Any deviation from this assumption requires further modification of the ETL settings displayed below. For example, if there is no “Local Currency” in the source data, you can add another master table – Company Master, which includes the Ledger and Local Currency columns, and then set up another %JoinTable step.

Expected Result

Amend Data DT(Data) to DB.Solution1d.Fact(Data) – the process output shall be stored into Data Building with DB Name = Solution1d.

You can set up an additional ETL using the Select Data command and the filter function that you learned from the solution “Solving Steep Learning Curve of ETL (1a)” to generate the following test report.

Standardise Unit of Measurement

Case SummaryApp Building

Standardise Unit of Measurement (1e)

Our group acquires a buying company every year. This is our management practice, so as not to interfere with their daily activities. This leads to operational problems at our headquarters related to the management of procurement data under various scenarios. The data file provided by each procurement office, they use different units of measurement.


Assuming that you understand the previous solution “Dual-base Currency (1d)”, your concern is slightly simpler than in the previous case, because the unit conversion factor, unlike the exchange rate, will not change with time.

In the solution below, we use different types of units of measure, and the JoinTable function, which you studied in previous solutions, allows you to standardise a single unit of measurement.

Block.1 Map Import Column with Common Column: It is used to allow different offices to use different layouts and column names.

Block.2 Standardise Unit of Measurement to KG: You see, we organised common functions for working with the same “Compute Column” command in different rows. Since there is a dependency between different calculated columns, the system is calculated from this order of two dimensions: from left to right and from top to bottom.

Block.3 Output Report with Standardise Measurement: You can add the Current column to select which column you want to display in the report. This feature also allows you to enter another column name different from the common column, for example, Ledger -> Company.

This solution are using below command functions: –

Sample Data

Below we provide three sets of sample data using different units of measure. However, the column name and layout are the same. You can try to change the layout and name of the column using the corresponding change in the above ETL settings.


Expected Result

The expected result indicates that the unit of measurement is standardised in kg. You can extend the ETL setting to cover currency handling as shown in solution 1d. If you want to change the display in the same way as shown below, you need to configure Block.3 to 2.ETL as Block.1, arrange the common column as you need.

Also See Related Demo App

To support your further understanding of solution 1a..1e, also see the demo app Purchase Consolidation for Global Metal Buying Offices.