3

I would like to update a column with different strings based on other strings in another column. For instance : if A, B, C in column 1, write Cat1 in column 2. So I use "case when". But even without writing 'else' when it always replace all other values by NULL. Which means that if I then want to update again the column with "if X, Y, Z in column 1, write Cat2 in column 2", "Cat1" gets deleted in all the cells where it had been added. If I don't want to update all cells at the same time, how can I conditionally update certain cells while ignoring others (i.e. not deleting their content)?

1
  • 1
    Try using if-statement with a nested CASE – Taras Aug 28 '20 at 10:39
4

if("Column 1" IN ('X','Y','Z'),'Cat2',"Column 2")

This checks whether the specified condition Column 1 is like X, Y, or Z is fulfilled and then either writes Cat2 into Column 2, or uses preexisting values or Column 2 otherwise.

You could also chain several if statements, or run a comprehensive case when when when when expression.


Similar to your question from earlier:

CASE
   WHEN "l_co" in ('Romainville','Bagnolet' ,'Bobigny','Bondy' ,'Les Lilas','Montreuil','Noisy-le-Sec' ,'Le Pré-Saint-Gervais','Pantin')
   THEN 'Est Ensemble'
   WHEN "l_co" in (several other city names)
   THEN 'Plaine Commune group'
   ELSE "EPCI"
END
1
  • Thank you that's perfect. What I was missing was writing ELSE "Column" (Column being the name of the column I am updating) which keeps in place other values. – Philippe Morgan Aug 28 '20 at 11:12
5

Why not not just sort and select, or filter, on the values you want to update and use "update selected" or "update filtered"? enter image description here

7
  • You are right, I think I'm just being stubborn in trying to see if there is a way with the Field calculator :-) – Philippe Morgan Aug 28 '20 at 11:02
  • Would you know how to select several values at the same time to filter results? For example in the "city column" select all instances with "Rome" and "Paris"? – Philippe Morgan Aug 28 '20 at 11:05
  • 1
    I respect that stubbornness ;-) – M Bain Aug 28 '20 at 11:06
  • Re. the filter for multiple values, its the same syntax as Erik has used in the accepted answer: "Column 1" IN ('X','Y','Z') I have added a picture to the answer to show a "compound filter expression" – M Bain Aug 28 '20 at 11:12
  • 1
    If there is a way to do it with the filter form its eluding me :P – M Bain Aug 28 '20 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.