topic Re: help with set analysis in QlikView

Expression to count Unique field values from two tables

fashid — Fri, 25 Nov 2016 12:52:18 GMT

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

Re: help with set analysis

sunny_talwar — Fri, 25 Nov 2016 12:57:32 GMT

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

Re: help with set analysis

fashid — Fri, 25 Nov 2016 13:04:34 GMT

I may use it a gauge chart or text object

Re: help with set analysis

swuehl — Fri, 25 Nov 2016 13:33:47 GMT

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

Re: help with set analysis

krishna20 — Fri, 25 Nov 2016 14:09:25 GMT

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

Re: help with set analysis

fashid — Fri, 25 Nov 2016 16:43:19 GMT

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 .

Re: Expression to count Unique field values from two tables

swuehl — Fri, 25 Nov 2016 17:50:52 GMT

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:

LOAD * INLINE [

EMPNO, STATUS_A

1, SUCCESS

2, SUCCESS

3, SUCCESS

4, SUCCESS

5, SUCCESS

6, SUCCESS

];

LOAD * INLINE [

EMPNO, STATUS_B

1, SUCCESS

2, SUCCESS

7, SUCCESS

8, SUCCESS

9, SUCCESS

10, UNSUCCESS

];

PERFECT:

LOAD Recno() as EMPNO

AutoGenerate 10;

Re: Expression to count Unique field values from two tables

fashid — Fri, 25 Nov 2016 17:58:50 GMT

Thanks a lot Guru for showing the different approaches .