Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
can you post sample data or a sample application?
Hi Massimo,
Thank you for your reply. Please find the attached sample file.
Regards
Krishna
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
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
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
Hi Steve,
I tried what you suggested,but it's raising error as Field not found in the mapping table.Please suggest me
I had to guess what your Policy ID field name might be. Replace this with
your own Policy ID.
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
Hi Steve,
Please suggest me how to achieve this.It's very needed