topic Re: Expression If statement in QlikView

Expression If statement

bsbernabe — Wed, 25 Nov 2020 16:16:04 GMT

Hello There,

Can you help me/teach me how to formulate or how to use If in expression like below scenario? i need the value or formula like below list.

B1 | RB2

| RB3

Close | Close | Close

Blank | Blank | Close

Open | Close | Pending

Cause as of now only i have like below screenshot i don't know how to formulate for the column RB3 to get the value using if.

i try this for the Column(RB3) but i know its wrong

If ((B1) = Close) and If ((RB2) = Close) then RB3 = Close

If ((B1) = Blank) and If ((RB2) = Blank) then RB3 = Close

If ((B1) = Open) and If ((RB2) = Close) then RB3 = Pending

so what should i do?

Regards,

Bing

Re: Expression If statement

ahmar811 — Wed, 05 Jul 2017 07:37:34 GMT

Bing

put this expression into column 3(RB3)

=if(B1='Close' and RB2='Close','Close',

if(B1=Null() and RB2=Null(),'Close',

if(B1='Open' and RB2='Close','Pending')))

Regards

Ahmar

Re: Expression If statement

bsbernabe — Wed, 05 Jul 2017 08:01:50 GMT

Hi Ahmar,

Thank you so much for your time and fast support i really appreciate some formula is working but except in terms of NULL not working like below expression:

if([RB1]=Null() and [RB2]=Null(),'Close',

if([RB1]='Open' and [RB2]=Null(),'Pending')

if i use this the value of column RB3 is blank, i hope you support me in this part

Best Regards,

Bing

Re: Expression If statement

ahmar811 — Wed, 05 Jul 2017 08:30:45 GMT

Bing

Please find updated expression below I hope this will work for if it is not working please send me sample qvw file.

if((RB1=Null() or RB1='' or RB1=' ') and (RB2=Null() or RB2='' or RB2=' '),'Close',

if([RB1]='Open' and (RB2=Null() or RB2='' or RB2=' '),'Pending')

Regards

Ahmar

Re: Expression If statement

effinty2112 — Wed, 05 Jul 2017 08:42:23 GMT

Hi Bing,

Your logic boils down to if B1 ='Open' then RB3 = 'Pending'

B1	RB2	if(B1 = 'Open', 'Pending','Close')
Close	Close	Close
Open	Close	Pending
		Close

cheers

Andrew

Re: Expression If statement

bsbernabe — Wed, 05 Jul 2017 09:07:19 GMT

Hello Ahmar,

Here my sample QVW, Kindly see the attached file.

Best Regards,

Bing

Re: Expression If statement

sunny_talwar — Wed, 05 Jul 2017 12:09:48 GMT

What is wrong with the output you are getting?

Re: Expression If statement

bsbernabe — Thu, 06 Jul 2017 00:58:34 GMT

Hi Sunny,

Regarding the expression Ahmar share to me i really appreciate cause it is almost good except the Null part cause i try to use the expression

"if([RB1]='Open' and [RB2]=Null(), suppose to be 'Pending',"

but not working so i ask Him a favor to help me in that part you also can try to check my QVW Test attached file in my previous reply, you can see at Column RB3 NULL is not working.

Regards

Bing

Re: Expression If statement

Anil_Babu_Samineni — Thu, 06 Jul 2017 03:05:33 GMT

In synthetically or functional wise the Null() function won't work as expected if you have dashed or spaces or any special characters

I would ask you to do something like this

If(RB1='Open' , If(RB2=' ', 'Pending',"))

Re: Expression If statement

balar025 — Fri, 07 Jul 2017 04:42:10 GMT

Hi,

You can use below thing instead of if condition

Set NullInterpret = '';

Status:

Mapping Load * Inline

[

Logic,Status

Open|Close,Pending

Close|Close,Close

Close|Open,Pending

];

Load

ApplyMap('Status',B1&'|'&B2) as RB3;

Load * Inline

[

B1,B2

Open,Close

Close,Close

Close,Open

];

-Ravi Balar