Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
nayrlloyd
Contributor II
Contributor II

Generate values for all dates

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:

ItemNoDateUsedQtyUsed
301/1/20141000
301/3/20141200
301/4/20141330
301/6/20141469
201/2/20141176
201/3/20141376
201/5/20141447

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

ItemNoDateUsedQtyUsed
301/1/20141000
301/2/20140
301/3/20141200
301/4/20141330
301/5/20140
301/6/20141469
301/7/20140
201/1/20140
201/2/20141176
201/3/20141376
201/4/20140
201/5/20141447
201/6/20140
201/7/20140

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.

11 Replies
nayrlloyd
Contributor II
Contributor II
Author

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
MVP
MVP

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