Performance help

[gauthamchilled]

I'm using the below expression in variable and using in many charts, can anyone help in simplifying. records are about 5 millions. Help me in tuning performance please .

PFA

[avinashelite]

I didn't get your exact requirement , but try like this :

=if([Reportflag]='y' and Acctype=TypeName,-value,value)

[phaneendra_kunc]

Even i dont understand your requirement. And more over is your data model in your original app was built like the one in attached? meaning "Value" being key?

One thing i would suggest is to take that IF logic back to the script if at all possible. If that is not the case i would create some FLAG's in script and use SET ANALYSIS.

It will be nice if you can share more info

[gauthamchilled]

This is the expression I used in most of the charts:

if([Reportflag]='y', -if(Acctype=TypeName,value,-value),if(Acctype=TypeName,value,-value))

Help me to handle this in the script.

Since Acctype and typename are in two different tables, I don't know how to compare and derive the expression in script.

[avinashelite]

if you want to achieve this in the script level you need to do a JOIN , join on 1 million row would not idle because it may recreate lot of data and the size of the file may get increase .

[phaneendra_kunc]

You can use APPLY MAP to create a new field called "new_value" in the script and just use "Sum(new_value)" in front end chart.

TypeMap:

Mapping Load *, 1 as Flag;

Load *Inline

[

TypeName

Type3

Type7

];

LargeTable:

Load *, if(Reportflag='y', if(ApplyMap('TypeMap',Acctype,0) = 1, -1*value, value),if(ApplyMap('TypeMap',Acctype,0) = 1, value, -1*value)) as new_value;

Load * inline

[

Company,Acctype,value,Reportflag

AAA,Type1,100,y

BBB,Type2,140,y

CCC,Type3,270,n

ddd,Type7,156,y

];

[avinashelite]

phaneendra.kunche‌

if we have one to many relationship between the tables than Apply mapping will fail

[phaneendra_kunc]

Even in that case the expression he wrote in the UI will fail as well un less if he includes the combination of dimensions in the chart to get to a single Acoount Type & Type Name for the combination to validate TRUE/FALSE.

Above is one concept where he can minimize the burden on UI to calculate the expression using applymap. There might be more to create flags if there is 1-Many relations in the data. Given the example scenario and lack of requirement at this moment this is the only option i can think of.

I would like to hear your opinion on how to boost the performance on 5Mil dataset. Share if you know any other solution.

[avinashelite]

I agree with your point, without knowing the exact requirement it would be not idle to speak or suggest the solution let's wait for some more info