topic Re: Reload only one field in script table? in QlikView

Reload only one field in script table?

emmanueld — Wed, 16 Mar 2016 09:34:16 GMT

Hello,

I'm looking for a way, in the script, to reload only one field of a table that has already been alimented.

Example:

I want to have all the rows from SOURCE1 and from SOURCE2, and match them if possible (if not, leave the field Batch_Type or Batch_Ext_Code empty).

BATCH:

LOAD

Batch_Id,

Batch_Type

from SOURCE1;

OUTER JOIN

LOAD

Batch_Id,

Batch_Ext_Code

from SOURCE2;

After that load, I would like to replace the null values in Batch_Type field by 'Not Defined', without having to reload the entire content of the table into another table.

Is there a way to do so?

If you want more details, it's because I need afterwards to remove some rows in some parts of the UI thanks to set analyses based on some values of Batch_Type (Batch_Type -= {xxx}), and unfortunately this also removes the rows with null values.

Many thanks in advance

Emmanuel

Re: Reload only one field in script table?

marcus_sommer — Wed, 16 Mar 2016 11:49:58 GMT

Have a look here: Filling Default Values Using Mapping | Qlikview Cookbook.

- Marcus

Re: Reload only one field in script table?

emmanueld — Wed, 16 Mar 2016 12:50:44 GMT

Thank you Marcus. Is that the only way to do it? It is quite similar to what I did (except I used If(IsNull(FieldName), DefaultValue, FieldName) instead of using the 'MAP' statements).

Even though it's the only thing I could think of, reloading the table twice by making a copy seemed odd to me, so I was thinking there might be another way (I'm quite new to Qlikview so I may not have all the reflexes!).

Re: Reload only one field in script table?

marcus_sommer — Thu, 17 Mar 2016 01:04:50 GMT

Most more efficient then:

If(IsNull(FieldName), DefaultValue, FieldName) will be If(len(trim(FieldName))=0, DefaultValue, FieldName)

and it's easy to use but you will need to know where NULL could be happen and apply it to each single field. Another method by merging tables might be to use mapping with applymap() which avoids potential problems of join-approaches and which could have a default-value by no matchings - but it won't cover the duplication/reduction of records from inner/outer-joins which are more uncommon.

Further approaches to handle NULL are the use of variables like NullAsValue, NullDisplay and NullValue. The best overview to all these methods could you find here: NULL handling in QlikView.

- Marcus

Re: Reload only one field in script table?

emmanueld — Thu, 17 Mar 2016 09:57:44 GMT

Thank you very much Marcus, I will do with these solutions