topic Re: Issue with OR statement with an AND statement in QlikView

Issue with OR statement with an AND statement

fashid — Wed, 15 Feb 2017 16:14:16 GMT

Hi Friends,

I have an issue with writing an or statement.

currently below is my if statement

if(Sum(REQ)>0 and REQUIREMENT_NEW ='27041' or REQUIREMENT_OLD='27041','NOT APPLICABLE','APPLICABLE') AS REQUIREMENT

But this statement is not returning the results.

i want to know if for an order i have Sum(REQ)>0 and if either of REQUIREMENT_NEW or REQUIREMENT_OLD is equal to 27041

then it it should give NOT APPLICABLE.

Now i also know why i am getting wrong results.

I am getting both NOT APPLICABLE and APPLICABLE for that particular order because i have values other than 27041 in the REQUIREMENT_OLD for that particular order.

i have attached an instance of my sample data and my sample output required

Regards,

Nadeem

Message was edited by: Nadeem Shaikh

Re: How do i write an OR statement with an AND statement

Anonymous — Wed, 15 Feb 2017 16:20:46 GMT

You don't need to use an and, just evaluate the first condition then pass to another IF() if True;

if(Sum(REQ)>0, if(REQUIREMENT_NEW ='27041' or REQUIREMENT_OLD='27041','NOT APPLICABLE','APPLICABLE')) AS REQUIREMENT

Re: How do i write an OR statement with an AND statement

vishsaggi — Wed, 15 Feb 2017 16:27:47 GMT

Try this?

= if(Sum(REQ)> 0 AND (REQUIREMENT_NEW ='27041' or REQUIREMENT_OLD='27041'), 'NOT APPLICABLE', 'APPLICABLE') AS REQUIREMENT

Re: Issue with OR statement with an AND statement

fashid — Thu, 16 Feb 2017 04:48:42 GMT

Thank you for your reply gareth but this not work as i have multiple values for REQUIREMENT_OLD other than 27041

Re: Issue with OR statement with an AND statement

fashid — Thu, 16 Feb 2017 04:51:13 GMT

hI vISHWARATH ,

this does not work for the same reasons

Re: Issue with OR statement with an AND statement

Thu, 16 Feb 2017 05:06:12 GMT

Try This

if(Sum(REQ)>0 and ( REQUIREMENT_NEW ='27041' or REQUIREMENT_OLD='27041'),'NOT APPLICABLE','APPLICABLE') AS REQUIREMENT

Re: Issue with OR statement with an AND statement

Thu, 16 Feb 2017 05:11:55 GMT

can you pls share ur application

Re: Issue with OR statement with an AND statement

fashid — Thu, 16 Feb 2017 05:14:49 GMT

hi manoj,

i have attached the excel file along with output that is required

Regards,

nadeem

Re: Issue with OR statement with an AND statement

Thu, 16 Feb 2017 05:18:38 GMT

which u have shared the source file does't have REQ column

Re: Issue with OR statement with an AND statement

fashid — Thu, 16 Feb 2017 05:22:03 GMT

I have added the column

Re: Issue with OR statement with an AND statement

Anil_Babu_Samineni — Thu, 16 Feb 2017 05:25:43 GMT

Your expression ok to me, What was the wrong over here

Sum(REQ) -- Where do you use this, And can you tell us what was the metric you want to use over here

May be try below

if(Sum(REQ_NEW)>0 and (REQ_NEW ='27041' or REQ_OLD='27041'),'NOT APPLICABLE','APPLICABLE') AS REQUIREMENT

if(Sum(REQ_OLD)>0 and (REQ_NEW ='27041' or REQ_OLD='27041'),'NOT APPLICABLE','APPLICABLE') AS REQUIREMENT

Re: Issue with OR statement with an AND statement

Anil_Babu_Samineni — Thu, 16 Feb 2017 05:28:17 GMT

if(Sum(REQ)>0 and (REQ_NEW ='27041' or REQ_OLD='27041'),'NOT APPLICABLE','APPLICABLE') AS REQUIREMENT

Re: Issue with OR statement with an AND statement

fashid — Thu, 16 Feb 2017 05:49:10 GMT

Hi Anil,

I have used the same expression it works but my problem lies as REQ_NEW or REQ_OLD has values other than 27041 .

i want a logic such that if the value 27041 is found in either column it should give the out put not applicable and stop evaluating further values and keep them not applicable only.

please have a look at my attached excel

Regards,

Nadeem

Re: Issue with OR statement with an AND statement

Thu, 16 Feb 2017 09:38:12 GMT

Hi I tried this in backend,

did some work around for this ,it is coming up now

TEST:
LOAD *,
IF(test=Previous(test),'APP','NOT') AS RES ;

LOAD [order no],
REQ_NEW, K
REQ_OLD,
REQ_NEW&REQ_OLD as data,
if( wildmatch((REQ_NEW&REQ_OLD),'*27041*'), 'Match','not') as test,
output,
F5
FROM
[\\svrin000mbp01.asia.corp.anz.com\kumar12$\Downloads\New Microsoft Excel Worksheet.xls]
(biff, embedded labels, table is Sheet1$);

Test this once let me know

Re: Issue with OR statement with an AND statement

Anonymous — Thu, 16 Feb 2017 10:13:55 GMT

Are you doing this in the script or front end?

sum() isnt required in the script;

if(REQ>0, if(REQUIREMENT_NEW ='27041' or REQUIREMENT_OLD='27041','NOT APPLICABLE','APPLICABLE')) AS REQUIREMENT