Average in Scripting

[tan_chungkam]

Hi all,

Please find attached as my excel files.

My excel table

As the current period is February 2019, so those period (>3) are having empty exchange rate.

I would like to create a new column - average exchange rate as screenshot below.

my desired outcome

Is there anyway to do that in scripting instead of UI?

[Mauritz_SA]

Hi there

You can use the following (I created a sample Excel file on my desktop called ExchangeRateTest.xlsx):

LOAD
[Year],
AVG([Exchange Rate]),
[Base Currency]
FROM [lib://Desktop/ExchangeRateTest.xlsx]
(ooxml, embedded labels, table is Sheet1)
GROUP BY [Year],[Base Currency]
;

If you want to get it in addition to your initial table then you can create a Key in both tables that is a combination of the Year and the Base Currency and JOIN them.

Good luck.

Mauritz

[PrashantSangle]

Try below
Base:
Load * from Currency_table;

NoConcatenate
temp:
Load Year, Period, Exchange_rate,Base_currency
where len(trim(Exchange_rate))>0
;

Left join(Base)
Load Year,Period,Avg(Exchage_rate) as avg_exchange_rate,Base_currency
from temp
Group by Year,Period,Base_Currency;

Drop table temp;

Regards,
Prashant Sangle

[tan_chungkam]

@PrashantSangle ,

Thank you for your solution.

The script does not work.

Just wanna ask is typo error or new field for script in red color?

Left join(Base)
Load Year,Period,Avg(Exchange_rate) as avg_exchange_rate,Base_currency
from temp
Group by Year,Period,Base_Currency;

Drop table temp;

Thank a million.

[tan_chungkam]

@PrashantSangle ,

Please find attached my scripting.

my script

[Mauritz_SA · Accepted solution]

Hi

You can also use this (just replace the Lib connection with yours):

Table:
LOAD
[Year] & [Base Currency] AS Key,
[Year],
[Period],
[Exchange Rate],
[Base Currency]
FROM [lib://Desktop/ExchangeRateTest.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

LEFT JOIN (Table)
LOAD
Key,
Avg([Exchange Rate]) AS [Average Exchange Rate]
Resident Table
Group By Key;

Drop field Key;

Otherwise @PrashantSangle can help you ;).

Regards,

Mauritz

[PrashantSangle]

Hi @tan_chungkam ,

try @Mauritz_SA  solution. It will work.

Regards,

Prashant Sangle

[tan_chungkam]

@Mauritz_SA ,

Appreciate your solution.

It works prefectly.

Thank you.