topic Re: Convert into script in QlikView

Convert into script

paulwalker — Mon, 15 Feb 2016 16:04:10 GMT

HI Community,

I have many calculations in my Application.. I am using almost Aggr function in UI level.

Because i have performance issues..

this expression working fine for me.... but How to convert into script level..???

Thanks in Advance....

Re: Convert into script

alex_millan — Mon, 15 Feb 2016 16:19:59 GMT

Hi Paul,

just in case that may help.

Try this in script:

OriginalData:

LOAD * Inline [

KEY, Category, Amount

1, 18, 10

2, 18, 5

3, 18, 5

4, 22, 25

5, 22, 12

6, 23, 100

];

TotalCategory:

LOAD Category, Sum(Amount) As AmountCategory RESIDENT OriginalData Group by Category;

LOAD 'TOTAL' As Category, AmountCategory; LOAD Sum(AmountCategory) As AmountCategory RESIDENT TotalCategory;

FactTable:

LOAD Category, Amount, Lookup('AmountCategory','Category',Category,'TotalCategory') As AmountCategory, Lookup('AmountCategory','Category','TOTAL','TotalCategory') As AmountTotal

RESIDENT OriginalData;

Drop table OriginalData;

Drop table TotalCategory;

Using this, you will have in your fact table the total amount per category, and the absolut total amount.

Best regards.

Re: Convert into script

paulwalker — Tue, 16 Feb 2016 04:08:48 GMT

HI Alex,

My Average Expression

Sum(Total Category * Aggr(Sum(Amount), Category)) / Sum(Total Aggr(Sum(Amount), Category))

It should be 22.13

But i am not getting this value..

PFA

Re: Convert into script

alex_millan — Tue, 16 Feb 2016 13:10:29 GMT

Hi again,

Check the attached file.

Hope that's what you need.

Best regards

Re: Convert into script

alex_millan — Wed, 17 Feb 2016 14:55:26 GMT

Hi PaulWalker,

have you ckecked the file? Did you finally solve it?

If so, remember to close the thread by marking the answer you prefer as correct.

Cheers!

Re: Convert into script

paulwalker — Thu, 18 Feb 2016 03:09:43 GMT

Thank you