Build Calculated Dimension in Script

[bazzaonline]

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

[Gysbert_Wassenaar · Accepted solution]

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;

[Anonymous]

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