Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bvssudhakar
Contributor II

how to exclude null values in straight table

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

Capture.JPG


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

Capture1.JPG


Please help me out to achieve this


Thank you in advance



1 Solution

Accepted Solutions
Frank_Hartmann
Honored Contributor II

Re: how to exclude null values in straight table

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

Unbenannt.png

View solution in original post

9 Replies
Partner
Partner

Re: how to exclude null values in straight table

Hi,

Provide with the sample data

bvssudhakar
Contributor II

Re: how to exclude null values in straight table

Hi Aruna,

Please find the attachment

Partner
Partner

Re: how to exclude null values in straight table

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

bvssudhakar
Contributor II

Re: how to exclude null values in straight table

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

Partner
Partner

Re: how to exclude null values in straight table

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

Or

Try this In Your expression

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


Regards

Raman

bvssudhakar
Contributor II

Re: how to exclude null values in straight table

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

Partner
Partner

Re: how to exclude null values in straight table

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

Frank_Hartmann
Honored Contributor II

Re: how to exclude null values in straight table

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

Unbenannt.png

View solution in original post

bvssudhakar
Contributor II

Re: how to exclude null values in straight table

Hi Frank,

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