Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
at least 3 consecutive days macro
- Thread starter azaz123
- Start date
- Thread starter
- #3
Hi,
Iam new to cognos, Iam wprking in hospital environment where people pull reports on pateints medical history and insurance claims. I am using Impromptu and power play transformer. IQD from catalogs were used to build the power cubes. I need a way to find the patients who were serviced atleast for 3 consecutive days. I was using count>=3, but I do not know how to get # consecutive days.
Thanks.
Iam new to cognos, Iam wprking in hospital environment where people pull reports on pateints medical history and insurance claims. I am using Impromptu and power play transformer. IQD from catalogs were used to build the power cubes. I need a way to find the patients who were serviced atleast for 3 consecutive days. I was using count>=3, but I do not know how to get # consecutive days.
Thanks.
azaz123,
I don't believe there is a way of achieving what you require in Powerplay with what you currently have.
One way of producing a list would be to link the table which has the date information to itself twice, either in the catalogue using two aliases or in the database as a view, using patient id and date. By making the date joins on a consecutive day basis, an Impromptu report which references all three tables should return events which have three consecutive dates.
It should be noted that patients with records over more than three consecutive dates will return more than one row, so either grouping or a select distinct filter might be required. (If a patient has n consecutive days, where n is 3 or more, then there will be n-2 rows returned).
So if your table is called Event and the fields are patientid and eventdate, I'd make 2 aliases of Event and have equi-joins between
Event and EventAlias1 on Event.patientid=EventAlias1patientid and Event.eventdate = adddays(EventAlias1.eventdate,1)
and
EventAlias1 and EventAlias2 on EventAlias1.patientid=EventAlias2patientid and EventAlias2.eventdate = adddays(EventAlias2.eventdate,1)
Alternatively, since you're new to Cognos, perhaps you could approach your Database Administrator and ask him whether he could set up a view in the database along the above lines?
Furthermore, another suggestion may get posted.
soi la, soi carré
I don't believe there is a way of achieving what you require in Powerplay with what you currently have.
One way of producing a list would be to link the table which has the date information to itself twice, either in the catalogue using two aliases or in the database as a view, using patient id and date. By making the date joins on a consecutive day basis, an Impromptu report which references all three tables should return events which have three consecutive dates.
It should be noted that patients with records over more than three consecutive dates will return more than one row, so either grouping or a select distinct filter might be required. (If a patient has n consecutive days, where n is 3 or more, then there will be n-2 rows returned).
So if your table is called Event and the fields are patientid and eventdate, I'd make 2 aliases of Event and have equi-joins between
Event and EventAlias1 on Event.patientid=EventAlias1patientid and Event.eventdate = adddays(EventAlias1.eventdate,1)
and
EventAlias1 and EventAlias2 on EventAlias1.patientid=EventAlias2patientid and EventAlias2.eventdate = adddays(EventAlias2.eventdate,1)
Alternatively, since you're new to Cognos, perhaps you could approach your Database Administrator and ask him whether he could set up a view in the database along the above lines?
Furthermore, another suggestion may get posted.
soi la, soi carré
- Thread starter
- #5
- Status