Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for
Search instead for
Did you mean:
Creator

## Average in Scripting

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?

Labels (4)

• ### urgent

1 Solution

Accepted Solutions
Partner - Specialist

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

7 Replies
Partner - Specialist

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

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
Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

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.

Creator
Author

Please find attached my scripting.

my script

Partner - Specialist

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

try @Mauritz_SA  solution. It will work.

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Creator
Author

Appreciate your solution.

It works prefectly.

Thank you.

Tags