Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be just this
Count(DISTINCT {<CLI = {"=Len(CLI) > 5"}, Indicator = {1}>}CLI)
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.
Could you please tell me how to achieve this in the front end, using set analysis?
May be just this
Count(DISTINCT {<CLI = {"=Len(CLI) > 5"}, Indicator = {1}>}CLI)
Count({<Indicator={1}>}If(Aggr(len(CLI),CLI)>5, CLI) )
result:
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.
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))