Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
drudd75077
Contributor II
Contributor II

How to perform an operation on all columns

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



1 Solution
5 Replies
drudd75077
Contributor II
Contributor II
Author

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
Not applicable

NullasNull *;

Anonymous
Not applicable

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
Contributor II
Contributor II
Author

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

];