How to perform an operation on all columns

[drudd75077]

I have the following script that converts null values to zeros in the load editor. However, I don't know how many columns I might have in a future file. Is there any way to create some type of loop to perform this on all columns.

Table1:

Load

"Plant",

if(IsNull("01/2018"),0, "01/2018") AS "01/2018",

if(IsNull("02/2018"),0, "02/2018") AS "02/2018",

if(IsNull("03/2018"),0, "03/2018") AS "03/2018",

if(IsNull("04/2018"),0, "04/2018") AS "04/2018",

if(IsNull("05/2018"),0, "05/2018") AS "05/2018",

if(IsNull("06/2018"),0, "06/2018") AS "06/2018",

if(IsNull("07/2018"),0, "07/2018") AS "07/2018",

if(IsNull("08/2018"),0, "08/2018") AS "08/2018",

if(IsNull("09/2018"),0, "09/2018") AS "09/2018",

if(IsNull("10/2018"),0, "10/2018") AS "10/2018",

if(IsNull("11/2018"),0, "11/2018") AS "11/2018",

if(IsNull("12/2018"),0, "12/2018") AS "12/2018",

if(IsNull("13/2018"),0, "13/2018") AS "13/2018"

Source;

Maybe something like:

LET col_count = NoOfFields('Table1');

LOAD

FOR i=0 to $(col_count)

     if(IsNull(i),0, i) AS i

NEXT i

[sunny_talwar · Accepted solution]

May be use NullasValue

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptRegularS...

[drudd75077]

That works. However, I'm not sure what to switch it back to null values. So currently I have my above statement replaced with:

NullAsValue *;

Set NullValue = 0;

Table1:

Load

*

Source;

I'm not sure how to switch it back to nulls showing up as nulls

I attempted:

NullAsValue *;

Set NullValue = Null();

but it's converting my null values to 'Null()' string. Which isn't what I want, can you help me how to change it back to representing nulls as nulls?

EDIT:

I tried:

NullAsNull *;

Table1_v2:

Load *

Source;

After my new statement and it seems to be converting all the nulls in Table1 & Table1_v2 back to null, I was hoping to see that Table1 would have zeros and Table1_v2 would have null. Just to ensure I know how to turn nulls back on.

[Anonymous]

NullasNull *;

[Anonymous]

I don't know what you're exactly trying, but if I remember it correctly, NullasNull/NullasValue works only for loaded fields, not for missing fields that will be converted into null() values when joining or concatenating. Then you must do a further resident load...

[drudd75077]

It looks like my edit and issue seems to have to do with the fact that I was using the same table. Overall I got an example of data to work and I understand how these items work better now. I really appreciate both of ya'll responses.

Here's a sample set of data I used to understand how this is working in case it's helpful to someone else later:

NullAsValue *;

Set NullValue = 0;

Set NullInterpret = '';

Test1:

LOAD * INLINE [

    RecordID, IncidentDate, Value

    1,, 100

    2, 05/02/2001, 154

    3, 12/03/2002,

    4, 09/08/2004, 53

];

NullAsNull *;

Test2:

LOAD * INLINE [

    RecordID2, IncidentDate2, Value2

    1,, 100

    2, 05/02/2001, 154

    3, 12/03/2002,

    4, 09/08/2004, 53

];