topic Re: Generic Load Null values in QlikView

Generic Load Null values

Fri, 20 Mar 2015 18:51:18 GMT

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!

Re: Generic Load Null values

prieper — Fri, 20 Mar 2015 19:16:37 GMT

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

Re: Generic Load Null values

Anonymous — Fri, 20 Mar 2015 19:50:58 GMT

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

load

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

Regards

Re: Generic Load Null values

petter — Fri, 20 Mar 2015 19:55:43 GMT

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

Re: Generic Load Null values

Fri, 20 Mar 2015 20:08:49 GMT

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!

Re: Generic Load Null values

Fri, 20 Mar 2015 20:42:22 GMT

Hi Melanie,

Have you tried to use function NullAsValue in load script ?

Re: Generic Load Null values

Fri, 20 Mar 2015 20:46:06 GMT

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.

Re: Generic Load Null values

Fri, 20 Mar 2015 20:52:07 GMT

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

Re: Generic Load Null values

prieper — Sat, 21 Mar 2015 08:09:35 GMT

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

Re: Generic Load Null values

Mon, 23 Mar 2015 15:07:08 GMT

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