topic Re: short way to write select statement to avoid loading empty columns in App Development

short way to write select statement to avoid loading empty columns

ynottableau — Thu, 29 Jun 2023 15:42:09 GMT

Hi, I often have to load from large databases that contains many columns with no data (null) and many times without knowing exactly what data are available.

I want to avoid having to load the data first, then checking each column, by dragging it into a table, to see if it is empty. It is very tedious.

Is there a short way to write a Select satement (say for SQL like databases) that will avoid loading empty column, without explicitly checking each column using 'len(column)>0.' Again There could be hundreds of columns.

Re: short way to write select statement to avoid loading empty columns

udit_k — Thu, 29 Jun 2023 16:19:29 GMT

We could use IS NOT Null and Coalesce function in sql statement to work with null values

IS NOT NUll:-

The IS NOT NULL condition is used to return the rows that contain non-NULL values in a column

SELECT FirstName, LastName ,MiddleName FROM Person.Person WHERE

MiddleName IS NOT NULL

Coalesce function :-

The COALESCE() function takes unlimited parameters and returns the first non-null expression in a list.

SELECT FirstName,LastName,Suffix,Title,

COALESCE(Title,Suffix,FirstName) AS NewValue

FROM Person.Person

Re: short way to write select statement to avoid loading empty columns

marcus_sommer — Fri, 30 Jun 2023 14:09:52 GMT

If you want to avoid the loading of empty fields you will need to check them before you start the load. This could be done by querying the system-tables of the data-base - which might be not always very simple as it could mean to combine multiple queries to get the needed information and/or requiring special access rights and may not be feasible by dynamic views.

Beside this you could remove the empty fields also after the load by querying the Qlik system-fields, something like:

for i = nooffields('table') to 1 step -1
let vName = fieldname($(i), 'table');
let vContent = fieldvaluecount('$(vName)');
if $(vContent) = 0 then
drop fields [$(vName)];
end if
next

You may need here an there some adjustments to the syntax and/or to your logic (excluding some fields, also looping against the nooftables() with a similar logic, ...) but the approach in general will work.