Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
tan_chungkam
Contributor

Average in Scripting

Hi all,

Please find attached as my excel files.

image.pngMy 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.

image.pngmy desired outcome

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

1 Solution

Accepted Solutions
Mauritz_SA
Contributor III

Re: Average in Scripting

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
Mauritz_SA
Contributor III

Re: Average in Scripting

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

Re: Average in Scripting

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.
tan_chungkam
Contributor

Re: Average in Scripting

@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
Contributor

Re: Average in Scripting

@PrashantSangle ,

Please find attached my scripting.

 

 

image.pngmy script

 

Mauritz_SA
Contributor III

Re: Average in Scripting

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

Re: Average in Scripting

Hi @tan_chungkam ,

 

try @Mauritz_SA  solution. It will work.

 

Regards,

Prashant Sangle

Great dreamer's dreams never fulfilled, they are always transcended.
tan_chungkam
Contributor

Re: Average in Scripting

@Mauritz_SA ,

Appreciate your solution.

It works prefectly.

Thank you.