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.

tgjones43's avatar
tgjones43
Helper IV
7 years ago

Thanks Anonymous and Anonymous. I thought that it wasn't possible to do this using the replace all function. I'd prefer to not have to create new columns as I have so many of them. I think there may be a way to avoid having to replace values if I use a different query in a previous step, so I will try to work that out.

 

Thanks for your help.

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!