Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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?

Thanks in Advance,

ANDY

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

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

Miguel_Angel_Baeyens

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?

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.