Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mirlogic
New Contributor II

How to aggerate records based on value of an attribute

i know that Qlik aggerates within the data sheets records where attributes are common. what i need is to control that based on a condition. if the value is less than 150K then aggergate then if not split them out.  example below i need FA862517C2001 to show up as 1 record with their totals sumed together.  The othe duplicate Contract Numbers are greate than 150K so the need to be seperated.

thank you

Picture1.png

6 Replies
MVP
MVP

Re: How to aggerate records based on value of an attribute

Do you have more than one dimensions in your chart above?

mirlogic
New Contributor II

Re: How to aggerate records based on value of an attribute

yes, i have several more dimensions.

MVP
MVP

Re: How to aggerate records based on value of an attribute

Is it possible that one of those dimension's value is different for the two Contract Number dimension which repeats? When the two rows are combined what value will show up for the particular dimension which have different value in the two rows?

jerifortune
Contributor II

Re: How to aggerate records based on value of an attribute

How are you getting the total? Is it calculated at the sheet level or load script?

mirlogic
New Contributor II

Re: How to aggerate records based on value of an attribute

the other dimensions are the same, the only difference is the measure column "Total". I can get them either all roll up or to all be broken out into unique records, what need is the ability to condition that roll up base on the value of the "total" attribute.  All contract numbers that are the same with a "total" greater than 150K roll up all others break apart into separate records.

the totals are just loaded from the data set

jerifortune
Contributor II

Re: How to aggerate records based on value of an attribute

Can you try this.

tempData:

LOad * Inline [

dimension, measure

FA862517C2000, 134353.00

FA862517C2001,27090.00

FA862517C2001,101587.50

FA862517C2005,310000.00

FA862517C2005,5590000.00

];

Data:

Load *, dimension&'|'&RowPatNO as AggDimension;

lOAD *,if(dimension=previous(dimension),peek("RowPatNO")+1,1) as "RowPatNO"

Resident tempData order By dimension, measure Desc;

Drop Table tempData;

Then Use this expression to calculate the New measure

IF(measure>=150000, measure, Aggr(Sum(measure), dimension))

Result:

aggr measure.PNG