6 Replies Latest reply: Mar 24, 2011 11:00 AM by Miguel Angel Baeyens de Arce RSS

    General IF Query.

      Hello Experts,

      Please check this expression and let me know if something is wrong as I dont see the result for this.

      sum({< NPD_ID = {'ISNULL()'}, TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'}, NAL2_ID = {'6161'}, NAL3_ID = {'9307'}, FORECAST_TYPE = {'CURRENT'} >} FCT_GROSS_REVENUE)

      This returns 0 and I was wondering what should I write to make NPD_ID not equal to Null?

      Thanks in Advance,

      ANDY

        • General IF Query.

          Hi, you can try that?

           

          sum({< NPD_ID = {'ISNULL()'}, TIME_PERIOD_YEAR ={" = $(VCurYear)"}, TIME_PERIOD_PERIOD ={'2'}, NAL2_ID = {'6161'}, NAL3_ID = {'9307'}, FORECAST_TYPE = {'CURRENT'} >} FCT_GROSS_REVENUE)

          The diference us un TIME_PERIOD_YEAR.

            • General IF Query.

              Hello Gregori,

              The issue is somewhere with the NPD_ID as everything else works fine and it stopped after I added the NPD_ID. Just checkin how to write in Set Analysis to say IF NPD_ID is not equal to NULL then sum??

              Thanks for the quick response

                • General IF Query.
                  Miguel Angel Baeyens de Arce

                  Hello Andy,

                  A function in set analysis needs to be within $(function). If the result is not numeric (a string, several values, blanks, commas) then you need to quote it. What I'd try is the following

                   

                  sum({< NPD_ID = {"=Len(NPD_ID) = 0"}, TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'}, NAL2_ID = {'6161'}, NAL3_ID = {'9307'}, FORECAST_TYPE = {'CURRENT'} >} FCT_GROSS_REVENUE)


                  or depending on how the driver is dealing with null values

                   

                   

                  sum({< TIME_PERIOD_YEAR ={$(#VCurYear)}, TIME_PERIOD_PERIOD ={'2'}, NAL2_ID = {'6161'}, NAL3_ID = {'9307'}, FORECAST_TYPE = {'CURRENT'} > - < NPD_ID -= P(NPD_ID) >} FCT_GROSS_REVENUE)


                  Using the element function P().



                  Hope that helps

              • General IF Query.

                Hi

                You can try

                ISNULL(NPD_ID) = 0

                Regards

                Anders

                  • General IF Query.

                    Hello,

                    I was wondering why QV would ignore null values in a table field from DB? I checked the DB and I can see that I have some Null values which is called NPD_ID , so that I need to write an expression sum(sales) where NPD_ID = NULL(). But when I checked the NPD_ID field in my table it doesnt show the Null values at all...can anyone help please?

                      • General IF Query.
                        Miguel Angel Baeyens de Arce

                        Hello,

                        A null value is a value that doesn't exist. A listbox (or tablebox or anything) will show possible values in white, excluded values in grey, and selected values in green. That's ok, that's what all already know.

                        But a null value is not a value, so it cannot appear, because is not a "possible" value.

                        There are some variables you can set at the beginning of the script so all null values are interpreted as blanks, or spaces, or whatever:

                         

                        NULLASVALUE *; // Will deal all null values in fields as "something" rather than non-existingSET NullValue = 'NULL'; // Will interpret all Null values as the string NULL Table:LOAD null() AS NullField, // Comment the NULLASVALUE line, you will not see any value here Ceil(Rand() * 6) AS ValueField, Chr(64 + Ceil(Rand() * 3)) AS NameFieldAUTOGENERATE 10;


                        Now you do can select null values.

                        Hope that helps.