1 2 Previous Next 16 Replies Latest reply on Jul 18, 2018 9:13 AM by Andrew Bickert

# Filling NULL Rows with Values

Hello,

How do I fill all the NULL rows of "FUR" column with values of "FUR-FU-100002456"

My calculation is

IF [Product ID (group)] == "FUR"

THEN

[Product ID]

END

• ###### 1. Re: Filling NULL Rows with Values

Hi Mursid,

I am not quite sure I understand what you are asking. Are you saying you want to pull over the product ID of any product in the FUR Group, and if there is a null (Not in the FUR Group) then pull over the value "FUR-FU-10002456"? If that is the case you will need an else statement:

IF [Product ID (group)] == "FUR"

THEN

[Product ID]

Else

"FUR-FU-100002456"

END

Or are you wanting to pull over the Group name of each product ID?

Andrew

• ###### 2. Re: Filling NULL Rows with Values

I think you may be looking for the IFNULL() command:

IFNULL(FUR, "FUR-FU-100002456")

• ###### 3. Re: Filling NULL Rows with Values

in new calculate filed "FUR"

you can use this:

ifnull ( [FUR] ,'FUR-FU-100002456')

• ###### 4. Re: Filling NULL Rows with Values

That would just fill all the rows with "FUR-FU-100002456" What if there are other "FURs"

• ###### 5. Re: Filling NULL Rows with Values

That would just fill all the rows with "FUR-FU-100002456" What if there are other "FURs"

• ###### 6. Re: Filling NULL Rows with Values

That would just fill all the rows with "FUR-FU-100002456" What if there are other "FURs"

• ###### 7. Re: Filling NULL Rows with Values

From your original question, that is what it sounded like you wanted to do.

Are you trying to use the first 3 characters of the Product ID category to define "FUR"?

If STARTSWITH([Product ID], "FUR") then [Product ID] else "FUR-FU-100002456" end

• ###### 8. Re: Filling NULL Rows with Values

if the product id is "FUR-FU-100002456" then all the NULL will be filled with "FUR-FU-100002456" and if the product ID is "FUR-FU-10003347" then all the NULL will be filled with "FUR-FU-10003347" and so on and so forth.

• ###### 9. Re: Filling NULL Rows with Values

You can just create a new calculation column and use formula :

IFNULL(FUR, "FUR-FU-100002456").

What this will do is if in the FUR column any of the value is NULL, it will be replaced with "FUR-FU-100002456" else original value will be retained.

• ###### 10. Re: Filling NULL Rows with Values

In that case IFNULL(FUR,PRODUCT_ID ) will work.

• ###### 11. Re: Filling NULL Rows with Values

you need create a new field with the statement: " ifnull ( [FUR] ,'FUR-FU-100002456') "and replace the field FUR for the new field

• ###### 12. Re: Filling NULL Rows with Values

Hi Mursid,

So you are looking for more of something like this:

Andrew

• ###### 13. Re: Filling NULL Rows with Values

Yes! Exactly what I'm looking for. How do you do it? May I have the solution?

Thank you.

• ###### 14. Re: Filling NULL Rows with Values

Sure, Here is the calculation I am using:

IF FIRST()=0 then ATTR(

IF [Product ID (group)] == "FUR"

THEN

[Product ID]

END

) else LOOKUP(ATTR(

IF [Product ID (group)] == "FUR"

THEN

[Product ID]

END)

,FIRST()) END

Essentially we are using the basis of your FUR calculation to get the first FUR value and then using the FIRST() table calculation to get the first value within each Customer ID. We are then applying the New Fur table calculation off of the Product ID to repeat the First() value.

Let me know if you need it in a different version.

Andrew

1 2 Previous Next