topic IsNull in set analysis with variable in QlikView

IsNull in set analysis with variable

Thu, 11 May 2017 08:56:30 GMT

Hi guys, I hope you can help me out with this one:

I want to make a set analysis with a variable where Dim2 is null, but my code doesn't give me the correct results:

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

Re: IsNull in set analysis with variable

Anil_Babu_Samineni — Thu, 11 May 2017 09:00:40 GMT

check whether this is working?

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}>} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

It it is working for you, It should work

sum(aggr(ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, IsNull([Dim2]) = {0} >} aggr($(v_1), [Dim1], [Dim2])) / 1000, [Dim1], [Dim2]))

Re: IsNull in set analysis with variable

Thu, 11 May 2017 10:09:35 GMT

This code works, but it doesn't give me all the variables I need:

v_New = ONLY({<[Dim1]={'A', 'B', 'C', 'D'}, [Dim2] = {''} >} aggr($(v_1), [Dim1], [Dim2])) / 1000

In the expression:

sum(aggr($(v_New), [Dim1], [Dim3]))

When I change my variable code to yours, it gives me a red line beneath the {0} and no values

Re: IsNull in set analysis with variable

marcus_sommer — Thu, 11 May 2017 10:29:27 GMT

AFAIK this [Dim2] = {''} won't query NULL else if the field-value is empty which could be considered as a kind of NULL but it's not the same and not NULL for qlikview. Instead of this you could try something like this: [Dim2] -= {'*'}. Another approach could be to replace NULL with "real" values maybe with something like:

if(len(trim([Dim2])) = 0, '#NV', [Dim2]) as [Dim2]

More could you find here: NULL handling in QlikView.

- Marcus