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

    Identifying multiple blank fields

    jemma freeman

      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