topic Re: pull in blanks as zeroes? in QlikView

pull in blanks as zeroes?

nburton78 — Sat, 16 Nov 2024 02:05:02 GMT

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?

Re: pull in blanks as zeroes?

fkeuroglian — Wed, 30 Oct 2019 14:55:12 GMT

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

Re: pull in blanks as zeroes?

nburton78 — Wed, 30 Oct 2019 15:55:36 GMT

thanks that works great!