QlikView Performance issue with Aggr function

[sheker_amrutham]

Hi,

I am facing performance issue in QlikView application. I have attached sample QlikView document with less number of records. In the application, I have added a Straight table with calculated condition. Calculated condition is max number of records should not be more than 200(Its 100000 in my original application). I have calculated max number of records using variable vMaxRecordsReachedCondition and assigned 1 or 0 to the variable.

In my original application I have a Contract which has 4000 Customers and 13000 Products, which is leading to 50 million rows in QlikView application (It's Cartesian product between Customer and Product. Each and every customer is eligible for each and every products). While evaluating variable vMaxRecordsReachedCondition, QlikView application is hanging and we did not get any response.

vMaxRecordsReachedCondition = "=If(Not vAreAttributesSelected,0, Sum(Aggr(1, $(=If(vAreAttributesSelected, vSelectedAttributeNames, 0)))) <=vMaxRowLimit)"

Please refer attached application for more clarity.

Is there any alternate to the formula "Sum(Aggr(1, $(=If(vAreAttributesSelected, vSelectedAttributeNames, 0))))", which gives same results.

We are using 32GB RAM and 8 Core Processor.

[sunny_talwar]

May be this

=If(Not vAreAttributesSelected,0, If(vAreAttributesSelected, Count(DISTINCT $(=Replace(vSelectedAttributeNames, ',', '&')))) <=vMaxRowLimit)

[sheker_amrutham]

Hi Sunny Talwar,

I have tested with the new Expression, Still I am facing the issue. No change in performance.

Thanks for your reply

[sunny_talwar]

May be swuehl‌ can give his expert advice

[sheker_amrutham]

Hi stalwar1‌

To handle above scenario, I came up with a work around but I am facing issue with IF Condition when we have huge amount of data.

First I would like to give the scenario

below are the variable used in application,

vSelectedAttributeNames = [ContractName],[ContractNumber],[CustomerName],[CustomerNumber],[ProductName],[ProductNumber]

vMaxRowLimit = 50000

vAreAttributesSelected = 1

vTotalRecordsUpperLimit = If(Count(Distinct ProductName) * Count(Distinct CustomerName)>400000, 0,-1)

vMaxRecordsReachedCondition = If(Not vAreAttributesSelected,0, Sum(Aggr(1, $(=If(vAreAttributesSelected, vSelectedAttributeNames, 0)))) <=vMaxRowLimit)

I have added a straight table with calcualted condition and Layout Show Conditon as  "If($(vTotalRecordsUpperLimit)=0,0,$(vMaxRecordsReachedCondition))".

I have added a Text object with Layout Show Conditon as  "Not If($(vTotalRecordsUpperLimit)=0,0,$(vMaxRecordsReachedCondition))".

Please find the attached Zip file for more details.

I am facing issue with below expression,

If($(vTotalRecordsUpperLimit)=0,0,$(vMaxRecordsReachedCondition))

I have two contracts in the attached QlikView application. one contract has 6 records, In this case, variable vTotalRecordsUpperLimit is assigned with -1 and vMaxRecordsReachedCondition variable assigned with -1 and application is fetching 6 records and displayed.

Second contract has nearly 70 Million records, In this case vTotalRecordsUpperLimit variable is assigned with 0 and while evaluting  expression "If($(vTotalRecordsUpperLimit)=0,0,$(vMaxRecordsReachedCondition))" system is hanged. As vTotalRecordsUpperLimit is 0 it should return 0, but system hanged.

The same is highlighted in the application.I have tried with Pick function also but it resulted the same.

can any one share solution for the specified issue.