Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

load script calculate measure on dimenson in another table

Hello Experts!

i would need to do this in the Load Script of Qlik Sense.

I load a table (let's say TABLE1) with a SELECT statement with some numbers, and a TABLE2 with some specifications (again with a select statement). There is a key that connects the 2 tables.

I would like to apply this formula on TABLE1: aggr(expression, dimensions), but the dimesion is on TABLE2 and numbers of the expression in TABLE1.


what can i do?


Thank you!

3 Replies
vinieme12
Esteemed Contributor II

Re: load script calculate measure on dimenson in another table

"what can i do?"   

    Post some sample data that best represents your scenario and expected output so it's easier for people here to understand what you want to acheive

Not applicable

Re: load script calculate measure on dimenson in another table

[TABLE1]:

LOAD

f1key,

f2,

nicenumber

;

SQL SELECT.......

[TABLE2]:

LOAD

f1key,

nicedimension

;

SQL SELECT.......

now i would like to calculate the average of [nicenumber] by [nicedimension] in TABLE1, adding something like aggr(avg(nicenumber), nicedimension). I know that the aggr function does not work in the loadscript, but this output has to be calculated in the load script.

example input tables:

[TABLE1]:

f1keyf2nicenumber
234...3
235...2
236...4
237...5

[TABLE2]:

f1keynicedimension
234AAA
235AAA
236BBB
237AAA

Example desired output:

f1keyf2nicenumberNEW
23433.33
23523.33
23644
23753.33
vinieme12
Esteemed Contributor II

Re: load script calculate measure on dimenson in another table

[TABLE1]:

LOAD

f1key,

f2,

nicenumber

;

SQL SELECT.......

LEFT JOIN (TABLE1)

[TABLE2]:

LOAD

f1key,

nicedimension

;

SQL SELECT..

NOCONCATENATE

LOAD nicedimension,

             AVG(nicenumber)  as newnumber

RESIDENT TABLE1

GROUP BY nicedimension;

Community Browser