Record Number exists in Two categories

[Anonymous]

Hello Experts,

I am facing one issue in report, The record number showing in different categories, Below is the sample,

Total	Female	Category of Insurees	Record Number	Joiner Type
1	1	21-30	20639	-
1	1	61-70	17006	-
1	1	100+	17006	-
1	1	100+	20639

In the above table rec no 20639 showing in the categories 21 - 30 and 100+ as well and my expression is

Count({$<_TransSubType={'CONTRACT','JOINERS'},_YearMonth={'$(vCurrentMonth)'},Record_TYPE_CODE={'1'},[Gender EN]={'Female'}>} DISTINCT RecordNumber),

The record should be in one category, How can I avoid this, Please advise me.

Thanks,

Ram

[petter]

And your'e absolutely sure that you have correct categorization in your data?

Which of the coluns are dimensions and which are expressions?

Is it a straight table you are using?

Is the categorization done by hou in your script or is it from the sourcd data?

[Anonymous]

Hi Petter,

Thank you for the reply,

i am sure that the categorization is correct

dimension is [Category of insured Persons] and Counting the Record Number along with few filter conditions.

Yes, It is straight table.

Categorization done by me and it is in the script not from the source.

Regards,

Ram

[petter]

For me at least it is hard to see where the problem is without getting more information...

Is it possible for you to share a subset of your application that illustrates the problem?

[Anonymous]

Hello Peter,

Application is very big, Here I am sharing the script for category . Please check it.Let me know if further information required

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])=1,Dual('1',1),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>1 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=10,Dual('2-10',2),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>10 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=20,Dual('11-20',3),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>20 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=30,Dual('21-30',4),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>30 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=40,Dual('31-40',5),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>40 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=50,Dual('41-50',6),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>50 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=60,Dual('51-60',7),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>60 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=70,Dual('61-70',8),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>70 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=80,Dual('71-80',9),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>80 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=90,Dual('81-90',10),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>90 and If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])<=100,Dual('91-100',11),

If(If(IsNull([Company Size Actual]),[Company Size System],[Company Size Actual])>100,Dual('100+',12),Dual('N/A',13))))))))))))) as [Category of Insured Persons],

Regards,

Ram

[Anonymous]

Hello Peter,

First I defined the category with below script

If([Company Size Actual]=1,Dual('1',1),

If([Company Size Actual]>1 and [Company Size Actual]<=10,Dual('2-10',2),

If([Company Size Actual]>10 and [Company Size Actual]<=20,Dual('11-20',3),

If([Company Size Actual]>20 and [Company Size Actual]<=30,Dual('21-30',4),

If([Company Size Actual]>30 and [Company Size Actual]<=40,Dual('31-40',5),

If([Company Size Actual]>40 and [Company Size Actual]<=50,Dual('41-50',6),

If([Company Size Actual]>50 and [Company Size Actual]<=60,Dual('51-60',7),

If([Company Size Actual]>60 and [Company Size Actual]<=70,Dual('61-70',8),

If([Company Size Actual]>70 and [Company Size Actual]<=80,Dual('71-80',9),

If([Company Size Actual]>80 and [Company Size Actual]<=90,Dual('81-90',10),

If([Company Size Actual]>90 and [Company Size Actual]<=100,Dual('91-100',11),

If([Company Size Actual]>100,Dual('100+',12),Dual('N/A',13))))))))))))) as [Category of Insured Persons],

Then I used Concatenate function and concatenated with previously given script, Do you think Can I use Left join instead of Concatenate. Your analysis will help me to resolve the issue.

Regards,

Ram

[petter]

So are you doing a concatenation of the tables?

[Anonymous]

Yes, I did the concatenation because based on the company size the values will be append to respective category and, I have another concatenation for based on the payment the records will be append.

Regards,

Ram

[petter]

Doing concatenation might lead to the issue of getting more than one category.

[Anonymous]

Ok, Would you suggest me better function to resolve the issue.

Regards,

Ram