Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jfreeman
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 :-

  

fruitvegshopsupplierseason
applecarrotshop1supplierA
orange shop2 spring
pearlettuce supplierAwinter
grapecarrotshop2supplierCsummer
orange shop2supplierD
pearonion 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

   

fruitvegshopsupplierseasonResult
applecarrotshop1supplierA Not Applicable
orange shop2 springApplicable
pearlettuce supplierAwinterApplicable
grapecarrotshop2supplierCsummerNot Applicable
orange shop2supplierD Applicable
pearonion 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
sudeepkm
Valued Contributor III

Re: Identifying multiple blank fields

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

Load *,

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

from <your table>;

9 Replies
YoussefBelloum
Esteemed Contributor

Re: Identifying multiple blank fields

Hi,

here is another technique to detect NULL/BLANKS:

try and adapt it on your script:

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

ogautier62
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

jfreeman
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'

jfreeman
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

jfreeman
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

sudeepkm
Valued Contributor III

Re: Identifying multiple blank fields

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

Load *,

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

from <your table>;

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

jfreeman
New Contributor II

Re: Identifying multiple blank fields

thanks Sudeep that works perfectly!