Generate values for all dates

[nayrlloyd]

First post so forgive the dumb question.

I have data with values for for some dates, but I need to calculate a mean.  So I need to show all dates and show a zero value for the dates that have no activity.

For example, this is what I have:

ItemNo	DateUsed	QtyUsed
30	1/1/2014	1000
30	1/3/2014	1200
30	1/4/2014	1330
30	1/6/2014	1469
20	1/2/2014	1176
20	1/3/2014	1376
20	1/5/2014	1447

This is what I would like my script to generate (assuming I wanted all dates between 1/1/2014-1/7/2014):

ItemNo	DateUsed	QtyUsed
30	1/1/2014	1000
30	1/2/2014	0
30	1/3/2014	1200
30	1/4/2014	1330
30	1/5/2014	0
30	1/6/2014	1469
30	1/7/2014	0
20	1/1/2014	0
20	1/2/2014	1176
20	1/3/2014	1376
20	1/4/2014	0
20	1/5/2014	1447
20	1/6/2014	0
20	1/7/2014	0

I would like to generate this data table in the script.  So far I've been able to create a calendar file which contains all dates, but I can't quite figure out how to create a record for all dates for each ItemNo.

Any help would be appreciated.

[nayrlloyd · Accepted solution]

swuehl - I ended up using your first script example.  The DISTINCT qualifier was necessary.  Embarrassingly, the only thing that I needed to add was a GROUP BY statement on the initial data load.  Here is what I ended up with:

INPUT:

LOAD

     ItemNo,

     DateUsed,

     Sum(QtyUsed) as QtyUsed

FROM

C:\DataSource.xlsx

(ooxml, embedded labels, table is Sheet2)

GROUP BY ItemNo, DateUsed;

CALENDAR:

LOAD Date(makedate(2014)+recno()-1) as DateUsed AutoGenerate 7;

//Date values per ItemNo

JOIN (CALENDAR) LOAD Distinct ItemNo Resident INPUT;

//DROP Table INPUT;

//complete with known QtyUsed values per date and ItemNo

LEFT JOIN (CALENDAR) LOAD * Resident INPUT;

DROP Table INPUT;

//Clean up

RESULT:

NoConcatenate LOAD

  ItemNo,

  DateUsed,

  RangeSum(QtyUsed) as QtyUsed

Resident CALENDAR

Order By ItemNo, DateUsed;

DROP Table CALENDAR;

Thanks again to everyone for the help!

[swuehl]

Sure, if you don't need the detailed QtyUsed values, you can aggregate in the script.