topic Re: Identifying multiple blank fields in QlikView

Identifying multiple blank fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Identifying multiple blank fields

YoussefBelloum — Fri, 14 Sep 2018 13:11:49 GMT

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

Re: Identifying multiple blank fields

ogautier62 — Fri, 14 Sep 2018 13:13:58 GMT

Hi,

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

regards

Re: Identifying multiple blank fields

marcus_sommer — Fri, 14 Sep 2018 13:47:51 GMT

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

Re: Identifying multiple blank fields

Anonymous — Fri, 14 Sep 2018 14:18:16 GMT

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

Re: Identifying multiple blank fields

Anonymous — Fri, 14 Sep 2018 14:21:36 GMT

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

Re: Identifying multiple blank fields

Anonymous — Fri, 14 Sep 2018 14:22:49 GMT

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

Re: Identifying multiple blank fields

sudeepkm — Fri, 14 Sep 2018 14:25:14 GMT

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

marcus_sommer — Fri, 14 Sep 2018 14:43:45 GMT

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

Re: Identifying multiple blank fields

Anonymous — Fri, 14 Sep 2018 14:49:22 GMT

thanks Sudeep that works perfectly!