topic Set Analysis in QlikView

Set Analysis

krishna20 — Sun, 26 Apr 2015 07:16:05 GMT

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

Re: Set Analysis

maxgro — Sun, 26 Apr 2015 10:05:18 GMT

can you post sample data or a sample application?

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 10:14:58 GMT

Hi Massimo,

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

Regards

Krishna

Re: Set Analysis

stevedark — Sun, 26 Apr 2015 10:56:40 GMT

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

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 11:18:24 GMT

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

Re: Set Analysis

stevedark — Sun, 26 Apr 2015 11:38:58 GMT

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

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 12:00:30 GMT

Hi Steve,

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

Re: Set Analysis

stevedark — Sun, 26 Apr 2015 12:12:32 GMT

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

your own Policy ID.

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 12:17:36 GMT

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

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 13:15:55 GMT

Hi Steve,

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

Re: Set Analysis

krishna20 — Sun, 26 Apr 2015 13:49:06 GMT

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?

Re: Set Analysis

stevedark — Sun, 26 Apr 2015 19:16:52 GMT

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

Re: Set Analysis

stevedark — Sun, 26 Apr 2015 20:04:05 GMT

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

Re: Set Analysis

krishna20 — Mon, 27 Apr 2015 08:40:37 GMT

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.

Re: Set Analysis

stevedark — Mon, 27 Apr 2015 12:36:38 GMT

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.

Re: Set Analysis

krishna20 — Mon, 27 Apr 2015 17:37:32 GMT

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.

Re: Set Analysis

stevedark — Mon, 27 Apr 2015 19:35:17 GMT

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.

Re: Set Analysis

krishna20 — Mon, 27 Apr 2015 20:04:36 GMT

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.

Re: Set Analysis

stevedark — Mon, 27 Apr 2015 20:25:14 GMT

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.

Re: Set Analysis

krishna20 — Mon, 27 Apr 2015 20:47:56 GMT

Hi Steve,

Thank you very much for replying in time.

The value for that Pol_No no change.