Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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

General IF Query.

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

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.

Not applicable

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

Not applicable

General IF Query.

Hi

You can try

ISNULL(NPD_ID) = 0

Regards

Anders

General IF Query.

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

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.

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.