Issue with OR statement with an AND statement

[fashid]

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

[Anil_Babu_Samineni]

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

Or

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

[Anil_Babu_Samineni]

Or

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

[fashid]

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

[#5]

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

[Anonymous]

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