Autogenerate and from statement

jpapador — Mon, 14 Oct 2013 15:47:37 GMT

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

swuehl — Mon, 14 Oct 2013 15:58:15 GMT

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;

topic Autogenerate and from statement in QlikView

Autogenerate and from statement

Re: Autogenerate and from statement