topic Re: Reuse a formula in QlikView

Reuse a formula

Thu, 17 Jul 2014 09:43:45 GMT

Hi,

I've created 2 crosstabs with same dimensions, two dimensions exactly. I would like to create an another crosstab where the expression depend of the two last expressions.

Eg:

CT 1: Claims Treated

Type : Date 07/07/2014 08/07/2014 09/07/2014

Migration 8 10 5

Creation 5 20 16

Resiliation 51 0 2

CT 2: Claims Received

Type : Date 07/07/2014 08/07/2014 09/07/2014

Migration 50 26 20

Creation 48 29 31

Resiliation 26 5 51

CT 3: The formula is (CT2 - CT1)/2.6

Type : Date 07/07/2014 08/07/2014 09/07/2014

Migration

Creation

Resiliation

Is it possible to reference CT2 and CT1 in CT3 ? (The formula)

Thanks.

Re: Reuse a formula

Thu, 17 Jul 2014 10:20:07 GMT

I'd do it this way (1 table):

CT1:

load * inline [

Date, Migration, Creation, Resiliation, Claim_type

07/07/2014,8,5,51, 'Treated'

08/07/2014,10,20,0,'Treated'

09/07/2014,5,16,2, 'Treated'

07/07/2014,50,48,26, 'Received'

08/07/2014,26,29,5,'Received'

09/07/2014,20,31,51, 'Received'

];

Treated:

load

Date,

Migration as Migration_treated,

Creation as Creation_treated,

Resiliation as Resiliation_treated

resident CT1 where Claim_type='Treated'

;

left join load

Date,

Migration as Migration_received,

Creation as Creation_received,

Resiliation as Resiliation_received

resident CT1 where Claim_type='Received'

;

load Date,

(Migration_received-Migration_treated)/2.6 as Migration,

(Creation_received-Creation_treated)/2.6 as Creation,

(Resiliation_received-Resiliation_treated)/2.6 as Resiliation,

'Received-treated/2.6' as Claim_type

resident Treated;

drop table Treated;

cheers!!

Re: Reuse a formula

Thu, 17 Jul 2014 10:41:46 GMT

CT1 and CT2 are created during analysis, that is, based on data already loaded.

The CT1 and CT2 I took for example are just a result obtained after applying formula on initial loaded data.

Re: Reuse a formula

Thu, 17 Jul 2014 10:51:43 GMT

But you can modify your CT1 and CT2 to be in one table adding the field Claim type or something similar, am I right?.

Is better than having two tables. If you have two tables with the same name fields provoque syntetic keys that is bad for the model performance.

something like this:

new_CT1:

load

Date,

Migration,

Creation,

Resiliation,

'Treated' as Claim_type

resident CT1

;

drop table CT1;

load

Date,

Migration,

Creation,

Resiliation,

'Received' as Claim_type

resident CT2

;

drop table CT2;

Treated:

load

Date,

Migration as Migration_treated,

Creation as Creation_treated,

Resiliation as Resiliation_treated

resident new_CT1 where Claim_type='Treated'

;

left join load

Date,

Migration as Migration_received,

Creation as Creation_received,

Resiliation as Resiliation_received

resident new_CT1 where Claim_type='Received'

;

load Date,

(Migration_received-Migration_treated)/2.6 as Migration,

(Creation_received-Creation_treated)/2.6 as Creation,

(Resiliation_received-Resiliation_treated)/2.6 as Resiliation,

'Received-treated/2.6' as Claim_type

cheers, if it's not clear you can attach a sample of your model.

Re: Reuse a formula

marcus_malinow — Thu, 17 Jul 2014 11:04:05 GMT

Hi,

one approach I often take is to define my calculations in variables, and refer to these in my expressions.

So, a very trivial example

vCalcTotalSales = Sales

vCalcCostOfSales = Cost

In my expressions

Sum($(vCalcTotalSales))

Sum($(vCalcCostOfSales))

Margin = Sum($(vCalcTotalSales) - $(vCalcCostOfSales))