Expression to count Unique field values from two tables

[fashid]

Hi,

I have two tables which consists of employee id and status.

Basically i want to create an expression which counts unique no of employees in both the tables which have status as success.

In my output i should get EMPLOYEES WITH ID'S  1,2,3,4,5,6,7,8,9

EMPNO	STATUS_A
1	SUCCESS
2	SUCCESS
3	SUCCESS
4	SUCCESS
5	SUCCESS
6	SUCCESS
EMPNO	STATUS_B
1	SUCCESS
2	SUCCESS
7	SUCCESS
8	SUCCESS
9	SUCCESS
10	UNSUCCESS

Regards,

Nadeem

[sunny_talwar]

Where are you hoping to get this result? Chart or Text box object?

[fashid]

I may use it a gauge chart or text object

[swuehl · Accepted solution]

Count(DISTINCT If(STATUS_A = 'SUCCESS' or STATUS_B = 'SUCCESS', EMPNO))

[krishna20]

Hi,

t1:
load*inline
[EMPNO ,STATUS_A
1 ,SUCCESS
2 ,SUCCESS
3 ,SUCCESS
4 ,SUCCESS
5 ,SUCCESS
6 ,SUCCESS
];

Concatenate
t2:
LOAD*Inline
[
EMPNO ,STATUS_B
1, SUCCESS
2 ,SUCCESS
7, SUCCESS
8 ,SUCCESS
9 ,SUCCESS
10, UNSUCCESS
] WHERE NOT Exists(EMPNO);

EXPRESSIN IN TEXT BOX

=Count(if(STATUS_A='SUCCESS' OR STATUS_B='SUCCESS',EMPNO))

[fashid]

Thank you so much stefan that does it for me .

I have one more question would this be possible with set analysis

Count({<STATUS_A={'SUCCESS'}> + <STATUS_B={'SUCCESS'}>}Distinct EMPNO)

I was thinking on these lines but could not make it work .

[swuehl]

I assume you have just these two tables linked by EMPNO?

You could try a natural set like

=Count({<EMPNO = p({<STATUS_A={'SUCCESS'}>})+p({<STATUS_B={'SUCCESS'}>}) >}Distinct EMPNO)

If you want to proceed with your approach, it might work if you make EMPNO a perfect key in at least one table:

A:

LOAD * INLINE [

EMPNO, STATUS_A

1, SUCCESS

2, SUCCESS

3, SUCCESS

4, SUCCESS

5, SUCCESS

6, SUCCESS

];

B:

LOAD * INLINE [

EMPNO, STATUS_B

1, SUCCESS

2, SUCCESS

7, SUCCESS

8, SUCCESS

9, SUCCESS

10, UNSUCCESS

];

PERFECT:

LOAD Recno() as EMPNO

AutoGenerate 10;

[fashid]

Thanks a lot Guru for showing the different approaches .