topic Re: how to exclude null values in straight table in QlikView

how to exclude null values in straight table

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

Hi Experts,

I have created a straight table with following dimensions and expressions.

Dimensions: Department,Title Name,Skill Name,Enter Date, jobid.

Expression 1: =Count(DISTINCT{<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},JobClassid={4}>}jobid)

Expression 2: =Sum(Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, JobClassid = {4}, Date_Flag={'EnterDate'}>} NoOpenings), jobid))

For these dimensions and expression my chart has correct output see below

Now I added one more column,Expression is: =fabs(Interval(EnterDate - Today(),'d')).

When i added the column with this expression all null values are also coming. Why i am not understanding. See this image

Please help me out to achieve this

Thank you in advance

Re: how to exclude null values in straight table

Anonymous — Wed, 19 Sep 2018 09:53:25 GMT

Hi,

Provide with the sample data

Re: how to exclude null values in straight table

bvssudhakar — Wed, 19 Sep 2018 10:02:05 GMT

Hi Aruna,

Please find the attachment

Re: how to exclude null values in straight table

raman_rastogi — Wed, 19 Sep 2018 10:08:45 GMT

Hi Sudhakar

Here in your expression 1 and 2 you are grouping your joibId and NoOpenings and in 3rd expression your calculating intreval in detail level.

fabs(Interval(EnterDate - Today(),'d'))

Instead of expression make it calculated dimension.

share sample data or app to look more

Regards

Raman

Re: how to exclude null values in straight table

bvssudhakar — Wed, 19 Sep 2018 10:17:34 GMT

Hi Raman,

I have changed that expression as a dimension now it's working perfectly. But i have one small query

How can i show the total for that calculate column

Thank you in advance

Re: how to exclude null values in straight table

raman_rastogi — Wed, 19 Sep 2018 10:31:01 GMT

As per my knowlege there is No option are avilable to show total of dimension you can try some work around menstion in below link

Total with dimension value

Try this In Your expression

fabs(Interval(max(EnterDate) - Today(),'d'))

Regards

Raman

Re: how to exclude null values in straight table

bvssudhakar — Wed, 19 Sep 2018 10:44:18 GMT

Hi Raman,

It's not working. it is giving eeror in calculated dimension

And

I am using personal edition. So I can't open those files in that thread.

Is there any possible way?? Please suggest

Thank you

Re: how to exclude null values in straight table

raman_rastogi — Wed, 19 Sep 2018 13:10:20 GMT

In your data model there are some jobid which are not mapped with departments (shown in table chart)

If you do not want to show null value in your table you can suppress null values for Department dimension.

As we can not show total of dimension values ,if you want to show total you have to make expression

Try this in your expression

(Interval( Today()-max(EnterDate),'d'))

And change Total mode to sum of rows.

Regards

Raman Rastogi

Re: how to exclude null values in straight table

Frank_Hartmann — Wed, 19 Sep 2018 13:43:45 GMT

you can try leaving your fabs formula as expression (and change Total mode to sum of rows)

but change your first dimension to this:

=if((Aggr(Sum(DISTINCT {<Recruiter = {'Irfan Memon', 'Rajiv Ranjan', 'Aditi Saxena', 'Sneha Singh'}, JobClassid = {4}, Date_Flag={'EnterDate'}>} NoOpenings), jobid))>0,If(Len(Trim(Department)) > 0, Department),Null())

And supress Null Values for this first dimension

Re: how to exclude null values in straight table

bvssudhakar — Wed, 19 Sep 2018 14:47:09 GMT

Hi Frank,

It's working perfectly. Thank you, Thank you so much for your support