Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
bazzaonline
Creator
Creator

Build Calculated Dimension in Script

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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;


talk is cheap, supply exceeds demand
Anonymous
Not applicable

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