6 Replies Latest reply: Jun 7, 2017 7:20 AM by Sunny Talwar

# Excluding values with a particular length

Hi

I have the following data.

CLI , Indicator

12345,1

1245698,1

124788,0

12369,1

12369874,1

Now, I want to count the number of CLI's with indicator 1 and where length greater than 5.

Thanks

• ###### Re: Excluding values with a particular length

Lots of ways to achieve this, but best practice says, do the transformation in the script first, then your expressions in the front end are really simple.

```T1:
CLI, Indicator
12345,1
1245698,1
124788,0
12369,1
12369874,1
];

T2:
IF(len(CLI)>=5,1,0) as Over5Flag
Resident T1;

Drop Table T1;
```

Then in a text box:

```=count({<Indicator={1}, Over5Flag={1}>}CLI)
```

Hope this helps.

• ###### Re: Excluding values with a particular length

Could you please tell me how to achieve this in the front end, using set analysis?

• ###### Re: Excluding values with a particular length

May be just this

Count(DISTINCT {<CLI = {"=Len(CLI) > 5"}, Indicator = {1}>}CLI)

• ###### Re: Excluding values with a particular length

Count({<Indicator={1}>}If(Aggr(len(CLI),CLI)>5, CLI) )

result:

• ###### Re: Excluding values with a particular length

omar this is an option, but I would suggest not to use Aggr() function if we can do it another way.... if you want to use if, why not just this?

Count({<Indicator={1}>}If(Len(CLI)>5, CLI))

• ###### Re: Excluding values with a particular length

Couple of options here for you now.  If performance is important to you, pre process in the script and use binary flags to filter out the records you don't want.