QGIS - Have spreadsheet with col from 0% to 100% plus null want 100 bin gradient plus one more for null

Question

Newbie alert. Have a spreadsheet with no geo; has a col with values in there from 0% to 100%, plus several blanks/nulls.

Ideally would like a 100 step (bin) gradient fill from 0 (100% black) to 100 (100% white) and then color the nulls pale yellow (for oops...missing data)

Have no trouble with CSV import, .csvt files, using the spreadsheet plug in, etc. Have not had trouble with simple joins. [Other than wondering if I can join keys of different lengths using things like an expression Right(bigfield, 15) to chop down a jumbo key to a normal one in the second table?]

When the column comes in as STRING it messes up the binning because 1, 10, 100 in the land of strings are all adjacent in the sort order. (Or, do I have to tough it out and manually correct? Not bad on 1-10, worse for 1-100.)

When I make the col come in as INTEGER or DOUBLE the blanks/Nulls are assigned 0's and that masks the "missing data". Unacceptable because I need to "see" the missing data and not pretend that its there.

Ideas?

Also, having a really rough newbie time with the expression editor - doing simple things like converting string to numeric, decimal to integer, etc. I'm coming from spending way too many years with Excel and a VBA coding background (very lenient on type conversions, and very easy to get in and out of all the major databases.) I take it the "philosophy" in QGIS is quite different? When I'm in Expression Editor am I actually in the Python world? Any thoughts on how a VBA coder should be thinking over here? Stuff like single quotes vs double, upper and lower case, fundamental data types, casting, etc...I seem to be stumbling on all. Any "summary" of this type of thing anywhere? Or should I allocate some time to learning basic Python syntax and writing simple Python programs? (If so, do I need a huge IDE like Eclipse or can I have at it with Notepad?)

Answer 1

First of all, I would suggest you to save your layer as a new layer once you have performed the simple join, in order to properly save the new attached columns in the layer attributes table. To achieve that: in the TOC, right-click on layer to save > Save As... and select Esri Shapefile for instance, which is the most currently used format (unless you need a specific one).

Then, once you have casted your string column into integer type, you can convert 0 values in null values by using the Field Calculator window.

In this window, tick the Update existing field option and select your column to update. Then, enter the following query (assuming that the column storing your values to update is named col, otherwise, just replace "col" with your column name):

CASE
  WHEN "col" = 0 THEN NULL
  ELSE "col"
END

This will replace 0 values with null values and leave other rows unchanged.