topic Re: Build Calculated Dimension in Script in QlikView

Build Calculated Dimension in Script

bazzaonline — Wed, 24 Sep 2014 09:43:16 GMT

Hello,

I currently have a calculated dimension as per below;

=if(Aggr(Sum(Incurred),[ClaimNumber])<=0,'a 0/Negative',if(Aggr(Sum(Incurred),[ClaimNumber])>0 and Aggr(Sum(Incurred),[ClaimNumber])<=2000,'b 0-2000',if(Aggr(Sum(Incurred),[ClaimNumber])>2000 and Aggr(Sum(Incurred),[ClaimNumber])<=3000,'c 2000-3000',if(Aggr(Sum(Incurred),[ClaimNumber])>3000,'d 3000+'))))

Any ideas exactly what I would need to write to be able to add this into the script??

Thanks Paul

Re: Build Calculated Dimension in Script

Gysbert_Wassenaar — Wed, 24 Sep 2014 11:18:43 GMT

You will need a table that contains both fields Incurred and ClaimNumber. If that table does not exist then you will have to create a table that joins data from the original tables. Once you have a table that contains both fields you can create a new table that contains ClaimNumber and the sum of Incurred per ClaimNumber. And you can use a preceding load to create a new field with the a,b,c,d values:

SummaryTable:

LOAD *,

IF(SumIncurred<=0,

'a 0/Negative',

IF(SumIncurred<=2000,

'b 0-2000',

IF(SumIncurred<=3000,

'c 0-3000',

'd 3000+'

)

) as NewField

LOAD

ClaimNumber,

sum(Incurred) as SumIncurred

RESIDENT MySource

GROUP BY ClaimNumber;

Re: Build Calculated Dimension in Script

Anonymous — Thu, 16 Apr 2015 10:17:24 GMT

Hi guys,

I have some problem with my calculated dimension.

=if(aggr((totalCategoriasKpi),id_master)=1 ,Dual('1',1),

if(aggr((totalCategoriasKpi),id_master)=2 ,Dual('2',2),

if(aggr((totalCategoriasKpi),id_master)=3 ,Dual('3',3),

if(aggr((totalCategoriasKpi),id_master)=4 ,Dual('4',4), dual ('5+',5)))))

If I build in the script as the example above described ...

DTicket:

LOAD*,

if(totalCategoriasKpi=1, '1',

if(totalCategoriasKpi=2, '2',

if(totalCategoriasKpi=3, '3',

if(totalCategoriasKpi=4, '4','5+')))) as DCategoriasKpi

LOAD

id_master,

totalCategoriasKpi

RESIDENT DTicket

GROUP BY id_master;

When i load script i have error in this part (RESIDENT DTicket

GROUP BY id_master;)

Can you help me please?

Thanks in advance,

Pedro Lopes