Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
sunny_talwar

May be just this

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

View solution in original post

6 Replies
gsbeaton
Luminary Alumni
Luminary Alumni

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:

Load * inline [

CLI, Indicator

12345,1

1245698,1

124788,0

12369,1

12369874,1

];

T2:

Load *,

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.

Anonymous
Not applicable
Author

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

sunny_talwar

May be just this

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

OmarBenSalem

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


result:

Capture.PNG

gsbeaton
Luminary Alumni
Luminary Alumni

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.

sunny_talwar

omarbensalem‌ 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))