9 Replies Latest reply: Sep 14, 2018 10:49 AM by jemma freeman RSS

    Identifying multiple blank fields

    jemma freeman



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



      orange shop2 spring
      pearlettuce supplierAwinter
      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



      applecarrotshop1supplierA Not Applicable
      orange shop2 springApplicable
      pearlettuce supplierAwinterApplicable
      grapecarrotshop2supplierCsummerNot Applicable
      orange shop2supplierD Applicable
      pearonion winter



      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?