topic Re: IF-AND Statement for missing data using text box. in QlikView

IF-AND Statement for missing data using text box.

Anonymous — Mon, 27 Mar 2017 15:21:24 GMT

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-

Re: IF-AND Statement for missing data using text box.

Anil_Babu_Samineni — Mon, 27 Mar 2017 15:28:26 GMT

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.

Re: IF-AND Statement for missing data using text box.

Anil_Babu_Samineni — Mon, 27 Mar 2017 15:30:11 GMT

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

Re: IF-AND Statement for missing data using text box.

Anonymous — Mon, 27 Mar 2017 15:36:09 GMT

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?

Re: IF-AND Statement for missing data using text box.

sunny_talwar — Mon, 27 Mar 2017 15:37:07 GMT

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','')

Re: IF-AND Statement for missing data using text box.

Anil_Babu_Samineni — Mon, 27 Mar 2017 15:38:46 GMT

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

Re: IF-AND Statement for missing data using text box.

Anonymous — Mon, 27 Mar 2017 15:40:44 GMT

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

Re: IF-AND Statement for missing data using text box.

Anonymous — Mon, 27 Mar 2017 15:41:37 GMT

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.