Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean: Not applicable

## 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?

ANDY

1 Solution

Accepted Solutions  Employee

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 Replies Not applicable
Author

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. Not applicable
Author

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 Not applicable
Author

Hi

You can try

ISNULL(NPD_ID) = 0

Regards

Anders  Employee

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 Not applicable
Author

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?  Employee

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. Community Browser