Autogenerate and from statement

[jpapador]

Can you use an autogenerate and a from statement in the same load?  I have 3 columns, Dept Code, 2012 bed count and 2013 bed count.

What I want to do is have dept code and then a date for every day in the year so 1/1/2013 --> 12/31/2013 with the value from the corresponding bed count column for every date of the year.  Attached is an example of what I have currently.  Any help would be appreciated.

[swuehl · Accepted solution]

Maybe like this:

Units:

CROSSTABLE (Year,Count) LOAD

     [Department Code],

     [2012 Bed Count] as 2012,

     [2013 Bed Count] as 2013

FROM EX1.qvd (qvd);

Calendar:

LEFT JOIN (Units) LOAD text(Year(Date)) as Year, Date;

LOAD Date(Makedate(2012)+recno()-1) as Date

AutoGenerate 366+365;

drop field Year;

edit: And if you don't want to use a join:

Units:

CROSSTABLE (Year,Count) LOAD

     [Department Code],

     [2012 Bed Count] as 2012,

     [2013 Bed Count] as 2013

FROM EX1.qvd (qvd);

Units2:

LOAD *,

     date(MakeDate(num#(Year))+iterno()-1) as Date

Resident Units

while iterno() <= floor(yearend(makedate(num#(Year)))-yearstart(makedate(num#(Year))))+1;

drop table Units;