set analysis error

[alexpanjhc]

Hi Experts

I have the following set analysis

Sum({$<[DATA_TYPE]={'SUMMARY'}, [BOOK3YRS]={'3'}>}LONG_TERM_DISALLOWED_LOSS)

in the table , Book3Yrs are all null, so i'd expect to get a zero, but the result seems to ignore the book3yrs.

Why is that and how do i correct the issue?

thanks!

[QFabian · Accepted solution]

Hi @alexpanjhc , i tried modifyng your set analysis formula without good luke. Hope someone else have the answer.

But, i have 2 options for you that maybe works:

1.-

The problem is with the null values from the data, so you can make a little code in your load script, changing null to empty :

if(isnull(BOOK3YRS_1), '', BOOK3YRS_1) as BOOK3YRS

With that, your formula works fine.

2.-

Try with the normal IF :

Sum(if([DATA_TYPE] = 'SUMMARY' and [BOOK3YRS] = '3', LONG_TERM_DISALLOWED_LOSS))

with that your formula works fine too

[tresesco]

Your field has to have at least one value to get the desired value w.r.t the set analysis. Null is no-value, hence it doesn't check the filter condition (in symbol table). You can can try creating a blank value for null like below, and then it should work fine.

NullAsValue '*';

Set NullValue='';

[Vegar]

It is as @tresesco  is saying. You need at least one non null value in your data set. One way to achieve this is to adda "empty" row containing a (any) value to the BOOK3YRS-column. In my script blow I created  blank ' ' value on my final row (All other fields are set to null() in this example).

set NullInterpret ='';  //Used to ensure that I get correct null values from my empty inline[] values
LOAD * inline [
	DATA_TYPE, 	BOOK3YRS,	LONG_TERM_DISALLOWED_LOSS
	STRING, 	,			100
	NUM, 		,			200
	DATE, 		,			300
	SUMMARY,	,			600
	,  			" ",
];

[alexpanjhc]

thank you!  i should have thought about it.