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

[krishna20]

So far i checked in the youtube.

They have given the examples to apply APPLYMAP function for a single table. How can i try for two tables to link two dates?

[stevedark]

Hi Krishna,

Is that code copied directly from your load script?  You do not have any FROM statements, and there is a rogue comma after the CLM_LOSS_Month.

After this script runs are there dates in both the CLM_LOSS_DT and ESTIMATE_CLOSE_DT fields?  If not dates, what appears in them, or are they null?

If they are proper dates they will sort correctly in a list box if you set the sort criteria to numeric, if they are not the date compare will fail and we will need to convert to dates in the load script.

Steve

[stevedark]

Looking at the example you uploaded earlier it would appear that you may need to use the key you have created %CLM_TO_EST in your Mapping table.

The mapping load would be:

Map_EST:

Mapping LOAD

   %CLM_TO_EST,

   CE_CLOSE_DT

Resident Claim_Estimate;

The ApplyMap would need to use another level of preceding load:

Claim:

LOAD

     *,

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

     ;

LOAD

     *,

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

     ;

LOAD

     CLM_BI_ID & CLM_TRANS_ID as %CLM_TO_EST,

     CLM_BI_ID,

This code must be run in the correct order to work.  I notice that presently you have the Claim tab before the Estimate.  These will need to be swapped, and the MAPPING load would need to go between the two.

Steve

[krishna20]

Hi Steve,

Thank you very much.

I applied apply map and got the result. A new problem raised in this.I applied the same concept or script in my actual Data modelling .In the sample app there is selected value at policy No.This policy no data is not showing in my actual data modelling.What may be the problem is?

Actually In my project data modelling there is no direct link between Claim and Claim_Estimate tables. They both will be linked through other tables.This is the reason i applied Apply map at estimate table itself.Please check the attached sample app and guide me.

[stevedark]

Hi Krishna,

You would not expect the mapping tables to appear in your data model.  They are created in the load script, used to map data and then destroyed at the end of the script.  Please read my article on ApplyMap, which I believe covers this.

The location of the mapping tables in the script is important.  The mapping table must be created before it is used.  Presently both mapping tables are sitting on the final tab.

You need to order your load script such that each mapping table is created after the table it is loaded from (as you are loading Resident) but before you use it for the first time.  You may need to move tabs around to achieve this.

Hope that helps.

[krishna20]

Hi Steve,

I'm really very thank full to you.

I don't know where i'm going wrong. I followed all the steps you guided me.But no luck. The app attached previously getting exact result. In that i linked the tables directly which i need. This logic is not working in my actual datamodelling.I here by attached sample app as like my actual datamodelling. Please help me where i'm going wrong.Sorry i'm very much confused. Kindly , guide me where i can exactly use this script.

[stevedark]

You still have the ApplyMap statements before the MAPPING LOAD statements.

Also, you are Mapping a field onto a table you are then doing a resident load on.

At very least you need to;

Move the ApplyMap tab before the Claim_Estimate tab

Delete the MAPPING load for ESTIMATE_MAP - this achieves nothing

Remove the code ApplyMap ('ESTIMATE_MAP',CE_BI_ID,Null()) as  leaving just ESTIMATE_CLOSE_DT

I'm struggling to see from the objects on the page what you are expecting to appear where.  There are certainly issues in some of the expressions where you are not using aggregation functions around values.  You need to get values in both ESTIMATE_CLOSE_DT and CLAIM_LOSS_DT first.

Suggest removing everything that is superfluous from your script.  Unused fields, redundant comments etc..  When things are stripped back you will have more chance of getting things working.

[krishna20]

Hi Steve,

Can you please edit in the script where i need to apply this logic.I'm unable to achieve this, tried in different ways.Please ,suggest me.

[stevedark]

I have made some amends to your previous script, but it is hard to be sure things will work without the data sources to run against.

Hopefully this will take you a step forward.

[krishna20]

Hi Steve,

Thank you very much for replying in time.

The value for that Pol_No no change.