NULL values/Blank fields

hokmoc · May 19, 2021, 6:08am

Hi everyone,

I have a problem in metabase. When i have a column with numbers and blank fields, metabase interprets the blank values as 0, when i use the number/score field value. But it should interpret the blank fields as NULL.

How do i solve this problem?

BR

flamber · May 19, 2021, 8:40am

Hi @hokmoc
Post "Diagnostic Info" from Admin > Troubleshooting, and which database type you're querying.
And which database column type it is, and what the Field Type in Admin > Data Model is.

hokmoc · May 19, 2021, 8:44am

{
"browser-info": {
"language": "en-GB",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0",
"vendor": ""
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "5.4.0-72-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.6"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.18"
}
},
"run-mode": "prod",
"version": {
"date": "2021-04-27",
"tag": "v0.39.1",
"branch": "release-x.39.x",
"hash": "6beba48"
},
"settings": {
"report-timezone": null
}
}
}

The field value is number, and i have also tried score

flamber · May 19, 2021, 8:52am

@hokmoc

Which version of MySQL?
What is the actual database type of the column?

hokmoc · May 19, 2021, 10:06am

It is 5.7.

The database type of the column is innoDB

flamber · May 19, 2021, 10:33am

@hokmoc That's not the column type. Run this to show column information:

show columns from YOUR_TABLE

hokmoc · May 19, 2021, 10:37am

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
token	varchar(36)	YES	MUL	NULL
submitdate	datetime	YES		NULL
lastpage	int(11)	YES		NULL
startlanguage	varchar(20)	NO		NULL
seed	varchar(31)	YES		NULL
startdate	datetime	NO		NULL
datestamp	datetime	NO		NULL
996198X50X593AO00	varchar(5)	YES		NULL
996198X50X593AO01	varchar(5)	YES		NULL
996198X50X593AO02	varchar(5)	YES		NULL
996198X50X593AO03	varchar(5)	YES		NULL
996198X50X593AO04	varchar(5)	YES		NULL
996198X50X619	text	YES		NULL
996198X51X620AO00	varchar(5)	YES		NULL
996198X51X620AO01	varchar(5)	YES		NULL
996198X51X620AO02	varchar(5)	YES		NULL
996198X51X626	text	YES		NULL
996198X52X628AO00	varchar(5)	YES		NULL
996198X52X628AO01	varchar(5)	YES		NULL
996198X52X628AO02	varchar(5)	YES		NULL
996198X52X628AO03	varchar(5)	YES		NULL
996198X52X628AO04	varchar(5)	YES		NULL
996198X52X628AO05	varchar(5)	YES		NULL
996198X52X628AO06	varchar(5)	YES		NULL
996198X52X628AO07	varchar(5)	YES		NULL
996198X52X628AO08	varchar(5)	YES		NULL
996198X52X641	text	YES		NULL
996198X53X642AO00	varchar(5)	YES		NULL
996198X53X642AO01	varchar(5)	YES		NULL
996198X53X642AO02	varchar(5)	YES		NULL
996198X53X642AO03	varchar(5)	YES		NULL
996198X53X655	text	YES		NULL
996198X54X656AO00	varchar(5)	YES		NULL
996198X54X656AO01	varchar(5)	YES		NULL
996198X54X656AO02	varchar(5)	YES		NULL
996198X54X663	text	YES		NULL
996198X55X664SQ01	varchar(5)	YES		NULL
996198X55X664SQ02	varchar(5)	YES		NULL
996198X55X664SQ03	varchar(5)	YES		NULL
996198X55X664SQ04	varchar(5)	YES		NULL
996198X55X674	text	YES		NULL
996198X56X675SQ01	varchar(5)	YES		NULL
996198X56X675SQ02	varchar(5)	YES		NULL
996198X56X675SQ03	varchar(5)	YES		NULL
996198X56X685	text	YES		NULL
996198X57X686SQ01	varchar(5)	YES		NULL
996198X57X686SQ02	varchar(5)	YES		NULL
996198X57X691	text	YES		NULL
996198X58X692AO00	varchar(5)	YES		NULL
996198X58X692AO01	varchar(5)	YES		NULL
996198X58X692AO02	varchar(5)	YES		NULL
996198X58X692AO03	varchar(5)	YES		NULL
996198X58X769AO00	varchar(5)	YES		NULL
996198X58X769AO01	varchar(5)	YES		NULL
996198X58X769AO02	varchar(5)	YES		NULL
996198X58X769AO03	varchar(5)	YES		NULL
996198X58X777AO04	varchar(5)	YES		NULL
996198X58X777AO05	varchar(5)	YES		NULL
996198X58X777AO06	varchar(5)	YES		NULL
996198X58X700SQ01	varchar(5)	YES		NULL
996198X58X700SQ02	varchar(5)	YES		NULL
996198X58X703	text	YES		NULL
996198X59X714SQ01	varchar(5)	YES		NULL
996198X59X714SQ02	varchar(5)	YES		NULL
996198X59X714SQ03	varchar(5)	YES		NULL
996198X59X714SQ04	varchar(5)	YES		NULL
996198X59X714SQ05	varchar(5)	YES		NULL
996198X59X727	text	YES		NULL
996198X60X728SQ01	varchar(5)	YES		NULL
996198X60X728SQ02	varchar(5)	YES		NULL
996198X60X728SQ03	varchar(5)	YES		NULL
996198X60X728SQ04	varchar(5)	YES		NULL
996198X60X728SQ05	varchar(5)	YES		NULL
996198X60X728SQ06	varchar(5)	YES		NULL
996198X60X734	text	YES		NULL
996198X61X740SQ01	varchar(5)	YES		NULL
996198X61X740SQ02	varchar(5)	YES		NULL
996198X61X740SQ03	varchar(5)	YES		NULL
996198X61X740SQ04	varchar(5)	YES		NULL
996198X61X740SQ05	varchar(5)	YES		NULL
996198X61X740SQ06	varchar(5)	YES		NULL
996198X61X740SQ07	varchar(5)	YES		NULL
996198X61X740SQ08	varchar(5)	YES		NULL
996198X61X740SQ09	varchar(5)	YES		NULL
996198X61X751	text	YES		NULL

flamber · May 19, 2021, 11:04am

@hokmoc Which column are you having problems with?
Husk at jeg ikke ved noget som helst om din opsætning, så du bliver nødt til at forklare, hvis du spørger om hjælp, så jeg kan prøve at reproducere.

hokmoc · May 19, 2021, 11:09am

All varchar(5).

So if you have blank cells, the system interprets them as 0 in the average calculation

flamber · May 19, 2021, 11:26am

@hokmoc Well, that's not a numeric column. It's a string, so change the Field Type to "No semantic type".

hokmoc · May 19, 2021, 12:43pm

Where do i do that?

I can't change it in the database, because it's a system database. So i need to be able to caluculate averages base on the varchar type.

flamber · May 19, 2021, 12:57pm

@hokmoc You change Field Type in Admin > Data Model > (db) > (table).
We are currently moving away from the old style of everything in one setting and splitting it out to Casting and Semantic.

The column is a string. Metabase currently does not support casting as something different in the GUI. You would have to do that with SQL. Example:
select cast(some_varchar_column as integer) as col_converted from some_table

You could do this with a database View, so you don't touch the existing table, and then you would just let Metabase use that table instead.

It is by pure coincidence that MySQL allows doing aggregations on non-numeric columns - most other databases will throw casting errors.