fill in blank cells

[novice1]

Hi all,

I have data set where sales for some items are NULL. I then need to allocate store number for each record, but the records with NULL sales come back with the store number as blank. Is there a way solve the problem where all records would have store number allocated to them?

[echuong1]

These store values are null because they were not in one of the datasets. You can fill these in with a conditional statement. For example:

if isnull([store_number])

then [Right_store_number]

else [store_number]

endif 

[novice1]

thank you. this did not work. 

Basically i have data where i get sales for each category store number and separate data for specific stores i want to look at. I have tried formula example you gave me, but the result is still the same.

I have attached example workflow 

[echuong1]

The reason why you have store numbers that are not filled out, is because they are missing from your dataset altogether. You can see that you have 13 records without a store number. 

I created a formula to consolidate the store number values from both of the files. If the store number is present only in the L input, it will take that value. If it is present only in the R input, it will take that value. If the store number is missing from both inputs, it will be marked as "unidentified store number."

Hope this helps.

[novice1]

thank you. 

So if the store number is missing in L record will it create new line for each store from R record for every given criteria?

[novice1]

Apologies. Meant to ask if there is a way to make that if Store number is missing in both records a new line would be created for each criteria with store numbers from second input file with 0 sales

[echuong1]

No, you are essentially just adding additional information from your bottom reference table with the join. You'll notice that the total number of records at the end is the same as that of your top input.

If the store number was available in the L input and not the R input, it will just have data filled in for the original columns. It will not have data filled in for fields coming from the R input (region, NHM, etc.).

Sales are already 0 for all records missing a store number. Please review the output.

[novice1]

Thank you. not what was hoping for. Seems i must another way around the issue, as i need to have store number for each record even if sales are 0