How to handle multiple null values and replace with values within same column

[prosell8]

Hello Qlik Community,

I'm trying to replace null values in  column "Number" with values within the same column. I have another column "End Customer" that has customer number that matches the first column and has both null and data values. 

I'm using the below formula but isn't replacing the null values. 

If(IsNull([AAA Number]) and above([End Customer] = [End Customer]), above([AAA Number]), [AAA Number])

Any suggestions on how to do in the front end, or back end would make more sense?

[cristianj23a]

Hi Prosell8.

Your current logic will only replace null values when the value of "End Customer" in the current row equals the "End Customer" in the row above. If the condition isn't met, the null values will not be replaced. Additionally, the above() function won't work in a load script because it's a chart function, not a script function.

If you're looking to fill null values in the "AAA Number" field based on matching "End Customer" entries, it's better to achieve this through the backend (load script). Below is an example using a resident load to achieve this:

LOAD
[End Customer],
[AAA Number]
FROM YourDataSource;

TMP_TABLE:
LOAD
[End Customer],
Max([AAA Number]) as [Max_AAA]
RESIDENT YourTable
GROUP BY [End Customer];

JOIN (YourTable)
LOAD
[End Customer],
[Max_AAA]
RESIDENT TMP_TABLE;

DROP TABLE TMP_TABLE;

YourTable:
LOAD
[End Customer],
If(IsNull([AAA Number]), [Max_AAA], [AAA Number]) as [AAA Number]
RESIDENT YourTable;

DROP FIELD [Max_AAA] FROM YourTable;

Here's how the script works:

1.- Load your original data.
2.- Create a temporary table (TMP_TABLE) that contains the maximum (or non-null) value of "AAA Number" for each "End Customer".
3.- Join this temporary table to your original table.
4.- Recreate your original table by replacing null values in "AAA Number" with the maximum value for the corresponding "End Customer".
5.- Remove the temporary "Max_AAA" field.
Note: In this script I assume that for every "End Customer", there is at least one non-null "AAA Number". If an "End Customer" only has null "AAA Numbers", those null values will still be null after the script. If you need different behavior for such cases, you will need to modify the script accordingly.

Regarts.

[prosell8]

Thank you Cristian, this is very helpful. 

[cristianj23a · Accepted solution]

You're welcome Prosell8, I'm glad it's resolved.
If it was solved with the formula that I sent you, I would appreciate it if you put it as solved.
Greetings and success.