IF-AND Statement for missing data using text box.

[Anonymous]

Hi,

I am creating a Qlikview app in which different sales teams load their data every week. From time to time, a particular customer fails to load their information correctly before the scheduled publishing of the app so I would like to display a message to users whenever this occurs.

I have used the 2 expressions below in a text object of which neither has worked as I'd like it to.

IF(CUST_WEEK=3,IF(CUSTOMER='B',IF(SOLD_QTY=0,IF(CUST_YEAR=2012,'Data Not Available from this customer',''))))

=IF(CUST_WEEK=3 and CUSTOMER='B' and SOLD_QTY=0 and CUST_YEAR=2012,'Data Not Available from this customer','')

I am trying to get to do the following: If for Week 3 of 2012 customer B has a total of zero sales, display the message "Data not available from this customer" else blank.

I have attached an example qvw file which shows what I'm trying to do. Feel free to edit the example with a solution as I am only using the attached file to troubleshoot a solution for my main app.

The result I'm getting is blank even though the condition I set should return the message that data is not available. Any help with this would be greatly appreciated and if further explanation is needed I will try and provide.

Thanks,

-B-

[Anil_Babu_Samineni]

may be this?

IF(CUST_WEEK=3 and CUSTOMER='B' and SOLD_QTY=0 and CUST_YEAR='2012','Data Not Available from this customer','-') as Flag

Tested done working fine.

[Anil_Babu_Samineni]

The result I'm getting is blank even though the condition I set should return the message that data is not available. Any help with this would be greatly appreciated and if further explanation is needed I will try and provide.

From here, You don't have the value for SOLD_QTY = 0, That is the why Null return to your Flag

[Anonymous]

Hi Anil,

Thanks for the response. If I understand your solution correctly, I will need to include the expression in my edit script instead of the text box? From there I would use the "flag" field in my conditional statement within my text object?

Is this the correct logic?

[sunny_talwar · Accepted solution]

Why don't you use this?

=If(Sum({<CUST_WEEK = {3}, CUSTOMER = {'B'}, CUST_YEAR = {2012}>} SOLD_QTY) = 0, 'Data Not Available from this customer','')

[Anil_Babu_Samineni]

That is i will high recommend to you. We can achieve by Flag and Set Analysis as well.

[Anonymous]

Okay. I follow now. I believe you and Sunny both provided solutions that work well.

[Anonymous]

Awesome. You and Anil both provided great feedback. I believe your solution works best in context to what I am trying to achieve. Thank you for the help.