1

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?)

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.

  • 1
    (redo above) "after join, save layer as new layer" - not positive I understand?I see a save as option, with a wide range of possible file formats, (which one would be best) and will this append the join columns to the main attribute table so future joins will not be needed? Does QGIS have trouble with joins? Am I wrong in thinking that a QGIS project on launch has an associated "underlying" data model, which is built at launch and then mostly kept in memory? (similar to Excel's data model?) All the project does is hold the recipe to make it again? – rjlabs Jan 27 '16 at 21:19
  • You are right. The join will be kept in project memory BUT if you want to open the layer separately (for another project for instance), the joined columns will not be there... Concerning Save As, I have edited my post to make it more easy to understand on this point. Anyway, the Esri Shapefile is a standard format and should be used if you don't need a specific one. – wiltomap Jan 28 '16 at 10:01
  • Please, consider deleting your first comment which is pointless. – wiltomap Jan 28 '16 at 10:04
  • Did not know I could delete. Done. Thanks. – rjlabs Jan 28 '16 at 16:59

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.