Set Analysis

[krishna20]

Hi Folks,

I wrote an expression using if condition .It's taking too much loading time.

I wrote conditions in variables and calling them in the expressions too.But, still facing performance issue.

Sheet can't able to open in access point. Please help me to convert this expression in pure set analysis.

Please find my expressions.

Original one :

Sum(if(CE_EST_TYPE='1' and CE_CLOSE_YN='0' or  CE_CLOSE_YN='1' and CE_CLOSE_DT>CLM_LOSS_DT,CE_AMT_LC_1))

Variable :

vValue1 : CE_EST_TYPE='1' and CE_CLOSE_YN='0' or  CE_CLOSE_YN='1' and CE_CLOSE_DT>CLM_LOSS_DT

Expression :

Sum(if(vValue1,CE_AMT_LC_1))

Regards

Krishna

[maxgro]

can you post sample data or a sample application?

[krishna20]

Hi Massimo,

Thank you for your reply. Please find the attached sample file.

Regards

Krishna

[stevedark · Accepted solution]

One of the first things to note is that when comparing numeric values dropping the single quotes will be much more efficient.

Putting the code into the variable will have no impact whatsoever on the performance - the same code will be executed.

The biggest performance gain you will get is by moving the logic for CE_CLOSE_DT>CLM_LOSS_DT to the load script, rather than have it in the expression.  If both fields are in the same table this should be done as follows:

LOAD
  CE_CLOSE_DT,

  CLM_LOSS_DT,

  if(CE_CLOSE_DT>CLM_LOSS_DT,1,0) as CLOSE_AFTER_LOSS,

This could have a bigger impact than changing IF logic to Set Analysis.

Once done, the Set Analysis would be:

Sum({<CE_EST_TYPE={1},CE_CLOSE_YN={0}>}CE_AMT_LC_1) +

Sum({<CE_CLOSE_YN={1},CLOSE_AFTER_LOSS={1}>}CE_AMT_LC_1)

I've split this into two sum statements to make unit testing easier, as the two parts are mutually exclusive.  You could also combine the two sets in a single sum statement if you wanted.

Hope that all makes sense.

Steve

[krishna20]

Hi Steve,

Thank you for your valuable reply and make it sense now. But, the dates

CE_CLOSE_DT,

  CLM_LOSS_DT, are from two different tables. I can't merge it.

Please suggest me any other way.

Regards

Krishna

[stevedark]

Hi Krishna,

That being the case, you have a further problem, as your expression may be trying to compare many Close dates with many Loss dates.  This could be the cause of it taking an incredibly long time.

Is the close date on the Policy table, while the loss date is on the Claim table?

If the Policy ID is on both you could load the Policy table, then create a MAPPING table of the close date:

Map_PolicyClose:

MAPPING LOAD

    POLICY_ID,

      CE_CLOSE_DT

RESIDENT Policy;

Then map the close date onto the Claims table;

ApplyMap('Map_PolicyClose', POLICY_ID, null()) as POL_CLOSE_DT,

Then in a preceding load do the compare code:

LOAD
    *,

    if(POL_CLOSE_DT>CLM_LOSS_DT,1,0) as CLOSE_AFTER_LOSS

    ;

LOAD

    ...

Presently your code is also taking into account policies where there have been no claims, where by mapping the close date onto the claims table you are taking policies out of the equation.

There is more info on ApplyMap in this blog post:

http://www.quickintelligence.co.uk/applymap-is-it-so-wrong/

Steve

[krishna20]

Hi Steve,

I tried what you suggested,but it's raising error as Field not found in the mapping table.Please suggest me

[stevedark]

I had to guess what your Policy ID field name might be. Replace this with

your own Policy ID.

[krishna20]

Hi Steve,

I tried like below, Please correct me if i'm wrong,i'm new to this concept.

Claim_Estimate:

LOAD

        CE_BI_ID,

        CE_COMP_CODE,

      CE_CLOSE_DT

C:\Estimate.qvd(qvd);

Map_EST:

Mapping LOAD

CE_BI_ID,

CE_CLOSE_DT

Resident Claim_Estimate;

Claim:

LOAD *,

if(ESTIMATE_CLOSE_DT>CLM_LOSS_DT,1,0) as CLOSE_AFTER_Years;

LOAD

         CLM_BI_ID,

        ApplyMap('Map_EST',CE_BI_ID,Null()) as ESTIMATE_CLOSE_DT,

          CLM_LOSS_DT,

          Year(CLM_LOSS_DT) as CLM_LOSS_Yr,

        Month(CLM_LOSS_DT)as CLM_LOSS_Month,

C:\Claim.QVD(qvd);

Please correct me where i went wrong.

Regards

Krishna

[krishna20]

Hi Steve,

Please suggest me how to achieve this.It's very needed