Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
"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
[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 | |
[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;