Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
7 years ago
Solved

Replacing all non-null values

This is probably a fairly simple query, but I cannot work out how to do it.   How can I replace all values that are not null across multiple columns with the word Yes.   Thank you!
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Sorry.

     

    Then I would probably add a conditional column
    IF [column] = null then NULL

    ELSE [column]

     

    This calculated column will have your cleaned up values. Then you can hide the original one.

     

    As far as I know the "Replace value" function does not have what you need.

vissvess's avatar
vissvess
Helper V
7 years ago

tgjones43 Hi..

 

Just came accross the same scenario... I found out a way to do that...

 

1. Select all columns you want to transform at once.

2. Change data type to irrelevant type. say, if the column has only text, change it to whole number so that all non null values would be as "errors". 

3. Now, change the data type back to original text/whole number. (in your case to replace with 'yes', change it to text)

4. Transform>Replace error>'Yes'.

 

Thats it... Its done.. Hope it worked. Update how it worked...

bdub1976's avatar
bdub1976
Frequent Visitor
5 years ago

Thank you vissvess you have a great solution.  In my case I had survey results with multiple columns for one question.  

I needed the nulls to be 0 and anything with text to be 1.  Taking his advice I converted the text column to whole number, replaced nulls with 0, and then replaced errors with 1.  Viola!