topic Re: Calculate field with null in QlikView

Calculate field with null

Anonymous — Thu, 03 Jul 2014 02:44:57 GMT

I have two column that use these formula:

=sum({<[Code Type]={'Code V'},Detail={'Operation Activities'}>}HourSpent)

=sum({<[Code Type]={'Code V'},Detail={'Project'}>}HourSpent)

What formula should i use to get:

- code type is code v

- detail is null

Anonymous — Thu, 03 Jul 2014 02:47:46 GMT

=sum({<[Code Type]={'Code V'},Detail={"=isnull(Detail)"}>}HourSpent)

Anonymous — Thu, 03 Jul 2014 03:28:48 GMT

can't calculate.not sure why. what if calculate all code v except code v with detail project & operation activities?

Anonymous — Thu, 03 Jul 2014 06:47:37 GMT

Hi,

Yes, you can even do that if you have only "project" & "operation activities" in detail column. You may use below script.

Sum({<[Code Type]={'Code V'},Detail-={'Operation Activities', 'Project'}>} HourSpent)

PS: Added "-" after Detail field to exclude 'Operation Activities' and 'Project'

nirav_bhimani — Thu, 03 Jul 2014 06:52:53 GMT

Create a flag in the script

eg. if(len(Detail)=0,1,0)as FlagDetailNull

Then use this flag in the set analysis.

Regards,

Nirav Bhimani

ashfaq_haseeb — Thu, 03 Jul 2014 08:56:39 GMT

Filtering Null is a tricky part.

You can try {*} to select all values that should exclude the null value and - {''} should get rid of the blank values.

=sum({<[Code Type]={'Code V'},Detail={'*'}-{''}>}HourSpent)

Regards

ASHFAQ

er_mohit — Thu, 03 Jul 2014 09:07:06 GMT

Try this

=sum({<[Code Type]={'Code V'},Detail-={'*'}>}HourSpent)

or this

=sum({<[Code Type]={'Code V'},Detail={''}>}HourSpent)

or this

=sum({<[Code Type]={'Code V'},Detail={' '}>}HourSpent)