1 Reply Latest reply: Oct 14, 2013 12:16 PM by Stefan Wühl RSS

    Autogenerate and from statement

    Jared Papador

      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.

        • Re: Autogenerate and from statement
          Stefan Wühl

          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;