topic Re: Syntax using 2 Ifs. in QlikView

Syntax using 2 Ifs.

Mon, 04 Jan 2016 11:38:39 GMT

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

Re: Syntax using 2 Ifs.

HirisH_V7 — Mon, 04 Jan 2016 12:43:36 GMT

Hi,

Post your sample data and required output!

-Hirish

Re: Syntax using 2 Ifs.

sunny_talwar — Mon, 04 Jan 2016 12:43:45 GMT

May be this:

Count({<Nationality = {'a', 'A'}, [Final Process] = {"=Len(Trim([Final Process])) = 0"}>} Nationality)

Re: Syntax using 2 Ifs.

Tue, 05 Jan 2016 04:12:56 GMT

Thanks Sunny,

It says error in expression.

Final Process is null and not 0.

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

Based on my above example, the answer is 3

Is there any other solution

Re: Syntax using 2 Ifs.

Tue, 05 Jan 2016 04:14:15 GMT

Hi Hirish,

Below is my sample data:

Nationality	Final Process
a
a
A
b
3

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

Count nationality if it's A and the final process is null (and not 0)

The answer is 3

Re: Syntax using 2 Ifs.

Tue, 05 Jan 2016 05:13:21 GMT

Hi,

I keep getting error in expression. Reference to above:

Count(if([Nationality]='A,a' AND IsNull([Final Process]) Nationality))

Re: Syntax using 2 Ifs.

sunny_talwar — Tue, 05 Jan 2016 10:07:28 GMT

Can you share a sample to show where exactly it isn't working?

Re: Syntax using 2 Ifs.

ramasaisaksoft — Tue, 05 Jan 2016 10:40:23 GMT

Hi CJ,

y don't you try like below?

script : Upper(UserName) as UserName

then select the nationality as A or a both are same

and in text box expression is like =count(UserName)

Re: Syntax using 2 Ifs.

Peter_Cammaert — Tue, 05 Jan 2016 10:45:06 GMT

What about:

Count(if(Upper([Nationality])='A' AND IsNull([Final Process]), Nationality))

Re: Syntax using 2 Ifs.

Kushal_Chawda — Tue, 05 Jan 2016 10:46:16 GMT

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

Re: Syntax using 2 Ifs.

Tue, 05 Jan 2016 11:06:13 GMT

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

Here is the Data:

Nationality Final Process

A KKK

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,

Re: Syntax using 2 Ifs.

Kushal_Chawda — Tue, 05 Jan 2016 12:09:47 GMT

for your above data my expression is working

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

Re: Syntax using 2 Ifs.

MayilVahanan — Tue, 05 Jan 2016 12:17:29 GMT

Try like this

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

Nationality,Final Process

A,KKK

];

Nationality	=Sum(AFlag)
	3
A	3

Re: Syntax using 2 Ifs.

Tue, 05 Jan 2016 12:43:11 GMT

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.

Re: Syntax using 2 Ifs.

Kushal_Chawda — Tue, 05 Jan 2016 15:30:42 GMT

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

Re: Syntax using 2 Ifs.

Wed, 06 Jan 2016 05:13:59 GMT

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"

Re: Syntax using 2 Ifs.

sasikanth — Wed, 06 Jan 2016 06:07:18 GMT

hi,

Try some thing like that

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

Re: Syntax using 2 Ifs.

jagan — Wed, 06 Jan 2016 06:13:17 GMT

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.

Re: Syntax using 2 Ifs.

Wed, 06 Jan 2016 08:49:56 GMT

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:

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,

Re: Syntax using 2 Ifs.

Wed, 06 Jan 2016 08:50:49 GMT

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:

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