# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor II

## Identifying multiple blank fields

hi

I want to find away of identifying multiple fields that may have blanks so that i can exclude them from my data. Only one of the fields has to have a blank in order for it to warrant it being excluded. My thought is to identify them and create an additional field that highlights them to then select that as appose to just delete them straight out of the source. Ive been trying varioations of the if(isnull and also if(field=' ' but neither seems to be working. Example set of my data is :-

 fruit veg shop supplier season apple carrot shop1 supplierA orange shop2 spring pear lettuce supplierA winter grape carrot shop2 supplierC summer orange shop2 supplierD pear onion winter

some of these fields can be blank and should not be excluded under the condition. Im thinking if i can add an identifier field in it would look like this

 fruit veg shop supplier season Result apple carrot shop1 supplierA Not Applicable orange shop2 spring Applicable pear lettuce supplierA winter Applicable grape carrot shop2 supplierC summer Not Applicable orange shop2 supplierD Applicable pear onion winter Applicable

so example formula ive tried is :-

if(isnull(fruit, veg, shop, supplier) 'Applicable', 'Not Applicable') as Result

if(fruit=' ' or veg =' ' or shop =' ' or supplier =' ') 'Applicable', 'Not Applicable') as Result

As you can see season doesnt matter if its blank or not this should not be excluded. Can anyone suggest where i am going wrong?

thanks

1 Solution

Accepted Solutions
Valued Contributor III

## Re: Identifying multiple blank fields

please try this. Something like what Marcus did but I'm using if else.

if(if(len(fruit)>0,1,0)+if(len(veg)>0,1,0)+if(len(shop)>0,1,0)+if(len(supplier)>0,1,0)=4,'Not Applicable','Applicable') as Result

9 Replies
Esteemed Contributor

## Re: Identifying multiple blank fields

Hi,

here is another technique to detect NULL/BLANKS:

If(Len(Trim(the_field_you_need))=0, 'NULL') as FLAG

if you want to filter directly NULL/BLANK values on a field, try this:

If(Len(Trim(the_field_you_need))>0, the_field_you_need) as the_field_you_need

Valued Contributor II

## Re: Identifying multiple blank fields

Hi,

try if(len(trim(field))=0,.....       to test for null or blank value

regards

## Re: Identifying multiple blank fields

I'm not quite sure that I understand which number of existing field-values should be 'Applicable' or 'Not Applicable' but I would try something like this:

pick((-(rangecount(fruit, veg, shop, supplier)=4))+1, 'Not Applicable', 'Applicable') as Result

- Marcus

New Contributor II

## Re: Identifying multiple blank fields

thanks for your suggestion unfortunately this does not get me the result im looking for it appears to classify everythings as 'not applicable'

New Contributor II

## Re: Identifying multiple blank fields

thanks for your reply how would you suggest i apply this to several fields? would you do

if(len(trim(field))=0, (field))=0, (field))=0

or would i build the multiple fields in the brackets along the lines of

if(len(trim(field))=0,len(trim(field))=0, len(trim(field))=0

I ask as ive tried this before raising to the Qlik community and wonder if it didnt work because im not building it out correctly to account for multiple fields

thanks

New Contributor II

## Re: Identifying multiple blank fields

thank you for taking the time to respond can you advise the best way to apply this to multiple fields in one string please

Valued Contributor III

## Re: Identifying multiple blank fields

please try this. Something like what Marcus did but I'm using if else.

if(if(len(fruit)>0,1,0)+if(len(veg)>0,1,0)+if(len(shop)>0,1,0)+if(len(supplier)>0,1,0)=4,'Not Applicable','Applicable') as Result

## Re: Identifying multiple blank fields

It's depending to which kind of non-content your fieldvalues have. This could be real NULL or missing values or empty strings or any kind of spaces.

With real NULL my suggestion will work. By the other NULL-types it will probably not. In this case you could try if it worked with rangenullcount() or rangemissingcount() or rangetextcount(). If this doesn't worked you need to check each fieldvalue if there is any content maybe with something like:

pick((-(rangesum(len(trim(fruit))>0, len(trim(veg))>0, len(trim(shop))>0, len(trim(supplier))>0)=-4))+1, 'Not Applicable', 'Applicable') as Result

Another way to avoid the len-check approach would be to adjust these NULL's with the various NULL variables. The best start in understanding what NULL meant is here: NULL handling in QlikView.

- Marcus

New Contributor II

## Re: Identifying multiple blank fields

thanks Sudeep that works perfectly!