Identifying multiple blank fields

[Anonymous]

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

[YoussefBelloum]

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]

Hi,

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

regards

[marcus_sommer]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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>;

[marcus_sommer]

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

[Anonymous]

thanks Sudeep that works perfectly!