topic Re: How to ignore null in the expression in QlikView

How to ignore null in the expression

ashis — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

In my data source I have column Parent1, Parent2,Parent3,Parent4 . The value of this column sometime null when there is no Parent .

I have one gantt chart where I am trying the following expression,

=aggr(max(Endtime)-min(Starttime),JOBNAME,Parent1,Parent2=,Parent3=,Parent4😃 (similarly I have 5 expressions)

It works fine if all the coulumns such as Parent1, Parent2,Parent3,Parent4 have value , but it does not work when there is null value for any of the Parent. It shows no data.

How to write expression such a way that it would ignore if parent is null.

Thank you,

Re: How to ignore null in the expression

vishsaggi — Mon, 02 Jul 2018 12:03:58 GMT

Can you share your app if possible to look into? When you say Null you mean you are getting - dashes or empty strings? May be you can try like below or you might have to write the expressions calculated dimensions.

= IF(Len(Trim(Parent1)) = 0 OR Len(Trim(Parent2)) = 0 OR Len(Trim(Parent3)) = 0 OR Len(Trim(Parent4) = 0, aggr(max(Endtime)-min(Starttime),JOBNAME,Parent1,Parent2,Parent3,Parent4))

OR try like

=aggr(max({<Parent1 = {"*"} ,Parent2 = {"*"},Parent3 = {"*"}, Parent4 = {"*"}>} Endtime) - min({<Parent1 = {"*"} ,Parent2 = {"*"},Parent3 = {"*"}, Parent4 = {"*"}>}Starttime),JOBNAME,Parent1,Parent2,Parent3,Parent4)

Re: How to ignore null in the expression

niclaz79 — Mon, 02 Jul 2018 12:06:50 GMT

=aggr(max(Endtime)-min(Starttime),JOBNAME,Alt(Parent1,0),Alt(Parent2,0),Alt(Parent3,0),Alt(Parent4,0))

Re: How to ignore null in the expression

ashis — Mon, 02 Jul 2018 12:11:37 GMT

Hi , thank you for your reply.

I tried your expression however it did not work.

I did not try calculated dimension though.

Re: How to ignore null in the expression

ashis — Mon, 02 Jul 2018 12:12:28 GMT

Hi Niclas,

I want to aggregate if there is not null value. I tried your expression , but it did not work.

Re: How to ignore null in the expression

ashis — Mon, 02 Jul 2018 12:15:07 GMT

When I say null , after reloading the data in qlikview , I do not see any string ,it is blank in the straight table.

Re: How to ignore null in the expression

vishsaggi — Mon, 02 Jul 2018 12:23:25 GMT

Try in calculated dimension if not can you share your app with just that pivot table sheet?