Forum Discussion

g60dave's avatar
g60dave
Frequent Visitor
5 years ago

IF statement while ignoring blanks/null help

Hi All

I cant seem to crack this one. I have 5 columns and I'm trying to create an if statement which will result in either true of false.

 

i would like the statement to look at all the columns A to E and if the data matches and also ignores blanks/null data then the result is true

If any of the columns do not match while ignoring the blanks/null then the results should be false. 

 

 

thank you in advance to anyone that can offer assistance

 

 

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

    If columns A through E were unpivoted, this would be as simple as checking if DISTINCTCOUNTNONBLANK were greater than 1 or not.

     

    You might be able to do something similar along the lines of this

    COUNTROWS ( UNION ( { [A], [B] }, { [C], [D], [E] } ) ) <= 1

     

  • g60dave's avatar
    g60dave
    Frequent Visitor
    5 years ago

    thanks for the suggestions, an unpivoted table doesn't work in this case. This is a database connected to D365

     

    But I like the idea of a distinctcountno blanks. I just wish I knew how to make that work across one row? any other suggestions?

      • g60dave's avatar
        g60dave
        Frequent Visitor
        5 years ago

        Thanks Daniel, ive added a customn column

         

        But i don't think this works, as the columns I want to work with are from merge queries 

         

        Can you offer any more advice?

         

        To give you more information, the other table is user related and includes the country of the user. The table I included merge queries is to do with the projects, and shows which countries are fulfilling that position in each project