pull in blanks as zeroes?

[nburton78]

The report I'm working on right now, I am matching a part number and pick date to the inventory and inventory date to determine if inventory was on hand when the order was supposed to be picked.  The issue is the inventory report I have doesn't list the dates the inventory was at 0.  So when I try to look a the data in a table, it returns less lines.  It removes those without an inventory record match.  

I have a table that lists inventory at any given date, it looks like this:

Part number	QTY on Hand	Date
Z	50	3/18/2019
Z	50	3/19/2019
Z	50	3/20/2019
Z	50	4/15/2019
X	50	4/15/2019
Y	50	4/15/2019

For this specific report i'm working on, I'd like it to return 0 for any missing date.  any idea how that can be done?    

[fkeuroglian · Accepted solution]

Hi

To do this, you have to "create" dates that have 0 value

One solution could be create a master calendar in a table or excel that contain all the dates, then you can do a join and create a flag after join

if(isnull(value),0,value as newfield after create the join

let me know it it is clear

Fernando

[nburton78]

thanks that works great!