Generic Load Null values

[#1]

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!

[prieper · Accepted solution]

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

[Anonymous]

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

load

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

Regards

[petter]

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

[#5]

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!

[#6]

Hi Melanie,

Have you tried to use function NullAsValue in load script ?

[#7]

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.

[#8]

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

[prieper]

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

[#10]

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!!!