topic Re: aggr into script in QlikView

aggr into script

surajdhall — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have an aggr expression that i want to move into the script to improve the performance, but i am not sure.

The expression is in the Trend dimension column in the pivot tab.

Could you please help.

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 13:21:51 GMT

This is going to be a fairly huge initiative to carry this out in the script... are you sure you want to do this?

Re: aggr into script

surajdhall — Thu, 02 Nov 2017 13:31:12 GMT

Hi Sunny,

The expression is taking long time to calculate in UI. So i want to move it to backend. If it is very huge task, then can i move part of it to the backend.

Thanks!

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 13:34:42 GMT

I mean once you decide to move it...I would move the whole thing... but you realize that once you move this to the backend, your values won't really change based on selections? For instance if you select a monthyear, it may not respond to it the way you would expect if you keep this as a calculated dimension?

Re: aggr into script

surajdhall — Thu, 02 Nov 2017 13:40:28 GMT

Hi Sunny,

I am fine with that. Please help to move it to backend.

Thanks!

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 13:45:33 GMT

It will take some time, but will work on it....

Re: aggr into script

pradosh_thakur — Thu, 02 Nov 2017 13:48:58 GMT

Hi Suraj

Try this as your calculated dimension and see if it makes any difference or optimizes a little

=Aggr(If(FirstSortedValue(TOTAL <Product, Status>

Aggr(Round((sum(Margin)/sum(Sales)),0.0001)- (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005),

Product, Status, MonthYear), Aggr(MonthYear, Product, Status, MonthYear))

FirstSortedValue(TOTAL <Product, Status>

Aggr(Round((sum(Margin)/sum(Sales)),0.0001),

Product, Status, MonthYear), -Aggr(MonthYear, Product, Status, MonthYear)),

'Bad Trend',

pick(match(Sum(TOTAL <Product, Status>

Aggr(if(not IsNull(Trim(Below(sum(Margin)/sum(Sales)))),

If(round(Alt(Below(Sum(Margin)/Sum(Sales)), 0),0.0001) < (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001) - (Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005)),1,

0),0),

Product, Status,MonthYear)

),0,1)+1, 'Average Trend','Good Trend','Positive Trend'))

Product, Status)

regards

Pradosh

Re: aggr into script

surajdhall — Thu, 02 Nov 2017 14:18:46 GMT

Hi Pradhosh,

Thanks. I see some improvement in cal time, but it is still heavy to calculate.

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 14:57:55 GMT

Check the attached script

Data:

LOAD *,

Date(MonthStart(Date),'MMM-YYYY') as MonthYear

INLINE [

Product, Date, Margin, Sales,Status

A, 01/01/2017, 1184632, 4227592,High

A, 01/02/2017, 1273124, 4545040,High

A, 01/03/2017, 676862, 2272720,High

A, 01/04/2017, 678862, 2272720,High

A, 01/05/2017, 679962, 2272720,High

A, 01/01/2017, 592316, 2113796,Low

A, 01/02/2017, 592316, 2113796,Low

A, 01/03/2017, 592316, 2113796,Low

A, 01/04/2017, 592316, 2113796,Low

A, 01/05/2017, 592316, 2113796,Low

B, 01/01/2017, 5, 52676,High

B, 01/02/2017, 6, 62803,High

B, 01/03/2017,,,High

B, 01/04/2017, 12, 96803,High

B, 01/05/2017, 7, 5267,High

B, 01/01/2017, 5, 852676,Low

B, 01/02/2017, 63, 362803,Low

B, 01/03/2017, 105, 2552676,Low

B, 01/04/2017, 132, 6803,Low

B, 01/05/2017, 75, 567,Low

C, 01/01/2017, 24, 243,High

C, 01/02/2017, 24, 243,High

C, 01/03/2017, 371, 464,High

C, 01/04/2017, 353, 78,High

C, 01/05/2017, 22, 435,High

C, 01/01/2017, 224, 244,Low

C, 01/02/2017, 224, 246,Low

C, 01/03/2017, 39, 449,Low

C, 01/04/2017, 33, 738,Low

C, 01/05/2017, 212, 455,Low

D, 01/01/2017, 37769, 379069,High

D, 01/02/2017, 4219, 42795,High

D, 01/03/2017, 377669, 375069,High

D, 01/04/2017, 42459, 424795,High

D, 01/05/2017, 6569, 392469,High

D, 01/01/2017, 37739, 379269,Low

D, 01/02/2017, 42124659, 424795,Low

D, 01/03/2017, 37765269, 3795069,Low

D, 01/04/2017, 424659, 42734795,Low

];

Aggr1:

LOAD Round((sum(Margin)/sum(Sales)),0.0001)-(Round(Alt(Sum(Margin)/Sum(Sales), 0),0.0001)*0.005) AS Cal1,

Round((sum(Margin)/sum(Sales)),0.0001) as Cal2,

Previous(Product),

Previous(Status),

Product,

Status,

MonthYear

Resident Data

Group By Product, Status, MonthYear;

Aggr2:

LOAD *,

If(Product = Previous(Product) and Status = Previous(Status), If(Peek('Cal2') < Cal1, 1, 0), 0) as Cal3

Resident Aggr1

Order By Product, Status, MonthYear desc;

Left Join (Data)

LOAD Product,

Status,

FirstSortedValue(Cal1, MonthYear) as Cal1,

FirstSortedValue(Cal2, -MonthYear) as Cal2,

Sum(Cal3) as Cal3,

If(FirstSortedValue(Cal1, MonthYear) > FirstSortedValue(Cal2, -MonthYear), 'Bad Trend',

If(Sum(Cal3) = 0, 'Good Trend',

If(Sum(Cal3) = 1, 'Positive Trend'))) as Trend

Resident Aggr2

Group By Product, Status;

DROP Table Aggr1, Aggr2;

Re: aggr into script

surajdhall — Thu, 02 Nov 2017 15:07:56 GMT

I am very thankful to you. I really appreciate your time and effort.

Re: aggr into script

MK9885 — Thu, 02 Nov 2017 15:08:02 GMT

Hi Sunny,

I've quite similar request. I want to load count distinct ID as new field in Script.

I want to bring all ID' plus also bring count distinct ID's

I tried Group by but didn't seem to work in front end a I'm using Count against many dimensions in different charts.

Hope i'm clear in requesting my query...

Can you please suggest?

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 15:13:52 GMT

I would need to see data... just like surajdhall did

Re: aggr into script

MK9885 — Thu, 02 Nov 2017 15:18:52 GMT

The Data is as always confidential data plus the size of the data is 500M+ records.

I only need to count distinct id based on different dimensions...

Can I do..

Load

ID,

Count(Distinct(ID)) as CountID

Resident Table

Group by ID, Dimension1, dimension2, Dimension3 etc....?

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 15:23:07 GMT

It should work.... if you can mock up (again just like how surajdhall did... I am sure this is not his complete data and is not the actual data).... Brother you need to make some efforts if you need helpful responses.... just giving bits and pieces of information will not get you anywhere

Re: aggr into script

MK9885 — Thu, 02 Nov 2017 15:24:13 GMT

Ok, I'll try to make an effort and let you know

Thanks.

Re: aggr into script

sunny_talwar — Thu, 02 Nov 2017 15:26:25 GMT

Sounds like a deal