load script calculate measure on dimenson in another table

[#1]

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!

[vinieme12]

"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

[#3]

[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]:

f1key	f2	nicenumber
234	...	3
235	...	2
236	...	4
237	...	5

[TABLE2]:

f1key	nicedimension
234	AAA
235	AAA
236	BBB
237	AAA

Example desired output:

f1key	f2	nicenumber	NEW
234		3	3.33
235		2	3.33
236		4	4
237		5	3.33

[vinieme12]

[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;