Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.