General IF Query.

[#1]

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

[#2]

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.

[#3]

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

[#4]

Hi

You can try

ISNULL(NPD_ID) = 0

Regards

Anders

[Miguel_Angel_Baeyens · Accepted solution]

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

[#6]

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?

[Miguel_Angel_Baeyens]

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.