Syntax using 2 Ifs.

[#1]

Dear Qlikview community,

As mentioned in my previous post, i'm new in qlikview and I need your help to achieve below calculation

Here is the formula using excel

=COUNTIFS(B13:B20,"A",C13:C20,"")

Count nationality if it's A and the final process is null

Here is the sample data

Nationality	Final Process
a
a
A
b
3

I appreciate your help

[#2]

Hi, Thank you for your kind help, still "error in expression"

Here is the Data:

Nationality                         Final Process

A                                                  KKK

B

A

B

A

A

C

Requirements:

Count Final Process if it is null and Nationality is = A

Though there are 4 "A's, the answer should be 3 because the 1st record which is nationality A but the  final process in not null, hence it will not be counted.

I hope I explain it clearly.

Regards,

[Kushal_Chawda]

for your above data my expression is working

=count( if(lower(Nationality)='a' and len(trim([Final Process]))<=0,Nationality,null()))

[MayilVahanan]

Hi

Try like this

LOAD *, If(Upper(Trim(Nationality)) = 'A' and Len(Trim([Final Process])) = 0, 1) as AFlag INLINE [

    Nationality,Final Process

    A,KKK

    B,

    A,

    B,

    A,

    A,

    C,  

];

Nationality	=Sum(AFlag)
	3
A	3

[#5]

Thanks Kush,

Though the expression is ok the result is "0".  Is there something that I did wrongly?

Thanks Mayil, I will try your solution and let you know.

[Kushal_Chawda]

It will be difficult to say without looking at your sample. Would you be able to share it?

[#7]

Good morning Kush,

Below is the sample file as well as the result I would like to accomplish

BGID	Business Group	EmpNo	EmpName	Nationality	Div	CC Org Level	A. Code	Person Types	Final Process
85	Corporate   Core Business   Group		Emp 1	American	30	7209		Trainee
85	Corporate Core Business   Group		Emp 2	American	30	7206		REGULAR
85	Corporate Core Business   Group		Emp 3	American	30	7226		REGULAR	31-DEC-05
85	Corporate Core Business   Group		Emp 4	Canadian	30	7226		REGULAR	31-MAR-05
52	Corporate Core Business   Group		Emp 5	Sudanese	52	9900		REGULAR
85	Corporate Core Business   Group		Emp 6	Sudanese	30	7220		REGULAR
85	Corporate Core Business   Group		Emp 7	British	30	7600		REGULAR
85	Corporate Core Business   Group		Emp 8	British	30	7065		REGULAR
85	Corporate Core Business   Group		Emp 9	Egyptian	30	7006		REGULAR
Total Number of   Active Employee
American	2	Count Nationality field   is equal to American and Final Process field is Null
British	2	Count Nationality field   is equal to British and Final Process field is Null
American	1	Count Nationality field   is equal to American,  Final Process   field is Null and Person Type field is equal to "Trainee"

[sasikanth]

hi,

Try some thing like that

=count({<Nationality={'A'} >} IF( LEN( [Final Process])=0,Nationality))

[jagan]

Hi,

Try this expression

Count({<Nationality = {'a', 'A'}, [Final Process] -= {"*"}>} Nationality)

Note: The above expression will be shown as invalid expression in Qlikview but it works.

Regards,

Jagan.

[#10]

Thanks Jagan,

It did not work.

Below is the data sample

BGID	Business Group	EmpName	Nationality	Div	Person Types	Final Process
85	Corp	Emp 1	American	30	Trainee
85	Corp	Emp 2	American	30	REGULAR
85	Corp	Emp 3	American	30	REGULAR	31-DEC-05
85	Corp	Emp 4	Canadian	30	REGULAR	31-MAR-05
52	Corp	Emp 5	Sudanese	52	REGULAR
85	Corp	Emp 6	Sudanese	30	REGULAR
85	Corp	Emp 7	British	30	REGULAR
85	Corp	Emp 8	British	30	REGULAR
85	Corp	Emp 9	Egyptian	30	REGULAR

Requirements:

#1

Count Nationality field is equal to American and Final
  Process field is Null

Answer is 2

Requirements # 2

Count Nationality field is equal to
  American,  Final Process field is Null
  and Person Type field is equal to "Trainee"

Answer is 1

Regards,

[#11]

Good morning Kush

Resending the data sample:

Below is the data sample

BGID	Business Group	EmpName	Nationality	Div	Person Types	Final Process
85	Corp	Emp 1	American	30	Trainee
85	Corp	Emp 2	American	30	REGULAR
85	Corp	Emp 3	American	30	REGULAR	31-DEC-05
85	Corp	Emp 4	Canadian	30	REGULAR	31-MAR-05
52	Corp	Emp 5	Sudanese	52	REGULAR
85	Corp	Emp 6	Sudanese	30	REGULAR
85	Corp	Emp 7	British	30	REGULAR
85	Corp	Emp 8	British	30	REGULAR
85	Corp	Emp 9	Egyptian	30	REGULAR

Requirements:

#1

Count Nationality field is equal to American and Final
  Process field is Null

Answer is 2

Requirements # 2

Count Nationality field is equal to
  American,  Final Process field is Null
  and Person Type field is equal to "Trainee"

Answer is 1