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

IsNull in set analysis with variable

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]))

3 Replies
Anil_Babu_Samineni

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]))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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

marcus_sommer

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