Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Generic Load Null values

Hi everyone,

I have a bizarre issue with Null values using a Generic Load.  I need to be able to select Null values.  I have tried all the usual techniques such as:

  • Calculated Dimension:  if(isnull([field name]),’missing’,[field name])
  • Set NullValue = ‘<NULL>’; then after ODBC connection enter... NULLASVALUE [field name};
  • Apply Map in script

The above techniques work except in our latest attempt to bring data into QV...the only thing we can associate is this is the only time we have a used a Generic Load (may not be the culprit, but it is best guess at this point).  The attached QV is completely stripped, but it has enough information to test the Null values.  Your help is greatly appreciated.  Thanks!

1 Solution

Accepted Solutions
Highlighted
Master II
Master II

You may give it a try with OTHERSYMBOL and then load some add lines:

SET OTHERSYMBOL = +;

LOAD * INLINE [proposal_id, Why

  +, #N/A];

LOAD * INLINE [proposal_id, Why Detail

  +, #N/A];

LOAD * INLINE [proposal_id, Options

  +, #N/A];

LOAD * INLINE [proposal_id, What

  +, #N/A];

LOAD * INLINE [proposal_id, Where

  +, #N/A];

HTH Peter

View solution in original post

9 Replies
Highlighted
Master II
Master II

You may give it a try with OTHERSYMBOL and then load some add lines:

SET OTHERSYMBOL = +;

LOAD * INLINE [proposal_id, Why

  +, #N/A];

LOAD * INLINE [proposal_id, Why Detail

  +, #N/A];

LOAD * INLINE [proposal_id, Options

  +, #N/A];

LOAD * INLINE [proposal_id, What

  +, #N/A];

LOAD * INLINE [proposal_id, Where

  +, #N/A];

HTH Peter

View solution in original post

Highlighted
Contributor III
Contributor III

I would put the text in the records null from the script.

load

if(IsNull(Options)=(-1),'Missing',Options) as Options....

Regards

Highlighted
MVP
MVP

QlikView Generic Load is working as designed actually. It is supposed to get rid of Nulls to create a dense/sparse data modell which will retain the relationships between fields so there is no loss of information. Efficient storage is one of the key benefits of using Generic Load.

Why you get nulls in your visualisations/charts is because you combine various fields from different tables and then the Chart data generation process recreate the Nulls on-the-fly when the Chart "calculates". The Nulls are only an end-product in the chart and not something that is stored.

So what you perceive as bizarre has a reasonable explanation.

IF you really want to preserve the Nulls while using Generic Load then you have to convert your Nulls into something else that can represent your Nulls... a special value of some sort. But that defeats maybe the purpose of using Generic Load... I think....

This conversion has to be done in your load script after or while loading from the database and before you get to your resident generic load.

Hope this helps.

- Petter Skjolden

Highlighted
Not applicable

Hi Peter, this works!  However, do you know if there is a way to accomplish this without explicitly listing the field names?  Thanks so much for your help!  It is greatly appreciated!

Highlighted
Not applicable

Hi Melanie,

Have you tried to use function NullAsValue in load script ?

Highlighted
Not applicable

Yes.  That works for everything else, but not this table.  Of course the example attached is just an extremely small snippet of our actual file.

Highlighted
Not applicable

just to test, could you try to use this function for second load of this table from resident?

Highlighted
Master II
Master II

Hi Melanie,

it should be possible to loop through the resident table "churn_responses" and get a list of all distinct "churn_responses" and then to create one table for each value found:

SET OTHERSYMBOL = +;

ExtractClass: // get unique Class-values

  LOAD DISTINCT churn_class RESIDENT churn_responses;

FOR i = 0 TO NOOFROWS ('ExtractClass') -1

  LET sClass = PEEK('churn_class', i, 'ExtractClass'); // Fieldname

  // create one table for each class-value found

  LOAD * INLINE [proposal_id, $(sClass)

  +, Missing];

NEXT i

// Clean Up

  DROP TABLE ExtractClass;

  LET i=;

  LET sClass=;

edit: code slightly changed for spelling-mistake

HTH Peter

Highlighted
Not applicable

Hi Peter,

This somewhat works in that it creates the correct field name, but somehow my data is not correct.  Since the first solution works great, I'm going with that one.  THANK YOU SO VERY MUCH!!!