8 Replies Latest reply: Jul 9, 2014 6:09 AM by Rhona Corcoran RSS

    sum in load

    Rhona Corcoran

      Hi I still havent got very far with this. so here is the full script.  You can see where I am trying to sum Metric Key 3,4 and 5 where the TLMKey is 18 and the SLMKey is 142.  I know you have to group by somehow but not sure how.  If anyone had a minute could they help please.  Thanks

       

      EWTD:
      LOAD ActualValue,
      TLMKey,
      SLMKey,
      MetricDesc,
      MetricKey,
      //Acute hospital metrics
      if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and MetricKey=4),MetricKey) as Discharges,
      sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients,

       

      if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as [Reducing Health Care acquired Infection],
      if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey = 18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of Readmission],
      //Primary Care Metrics
      if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey <=4),MetricKey) as [Community Intervention Team by Source],
      if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as [Physio Wait >12 wks],
      if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or MetricKey <=79),MetricKey) as [OccTherapy wait> 16 weeks],
      if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey) as [Ortho No Wait 12 Mths],
      if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10 and SLMKey=101 and MetricKey=2),MetricKey) as [Ortho Wait >4 yrs],
      if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey <=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey <=16))),MetricKey) as Orthodontics,
      RDODesc,
      RDOKey,
      ReferredMM,
      ReferredYYYY,
      SGKey,
      SGName,
      ServiceProvider,
      ServiceProviderType,
      ServiceProviderNum,
      if(ServiceProviderType='H',ServiceProvider) as Hospital,
      if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,
      if(ServiceProviderType='O',ServiceProvider) as Team
      ;

       


      SQL SELECT *
      FROM Healthstat.dbo."MMI_ScorecardLevel3"
      where (tlmkey = 18 and slmkey = 142 and MetricKey=3) or (tlmkey = 18 and slmkey = 142 and MetricKey=4)or(tlmkey = 18 and slmkey = 142 and MetricKey=5)or (tlmkey = 18 and slmkey = 166 and MetricKey=4) or (tlmkey = 18 and slmkey = 200 and MetricKey=46 ) or (tlmkey = 10 and slmkey = 192 and MetricKey=20 )or (tlmkey = 18 and slmkey = 200 and MetricKey=4)or
      (tlmkey = 10 and slmkey = 103 and (MetricKey>=1 or MetricKey <=4))or (tlmkey = 10 and slmkey = 191 and (MetricKey>=68 or MetricKey <=79))or (tlmkey = 10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=2)) or (tlmkey = 10 and slmkey = 101 and (MetricKey>=11 or MetricKey <=12)) or (tlmkey = 10 and slmkey = 101 and MetricKey=10) or (tlmkey = 10 and slmkey = 101 and MetricKey=16)
      or (tlmkey=10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=4)) or (tlmkey=10 and slmkey = 101 and (MetricKey>=5 or MetricKey<=16))
      and (ServiceProviderType='H' or ServiceProviderType='T' or ServiceProviderType='O');

       


      MonthMapping:

       

      LOAD * INLINE [
      ReferredMM, Month

      1, Jan

      2, Feb

      3, Mar

      4, Apr

      5, May

      6, June

      7, July

      8, Aug

      9, Sept

      10, Oct

      11, Nov

      12, Dec

      ]
      ;

        • Re: sum in load
          Harshal Patil

          Try this:


          EWTD:

          LOAD ActualValue,

          TLMKey,

          SLMKey,

          MetricDesc,

          MetricKey,

          sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients

          from SourcePath Group By TLMKey,SLMKey,MetricKey;

            • Re: sum in load
              Rhona Corcoran

              Thanks Harshal

               

               

               

              Where do I put this do I put it after my full load or do you mean to put

              in the full EWTD Table and then group by at the end

               

               

               

              Sorry Confused

               

               

               

              Rhona

               

               

               

              Rhona Corcoran

               

              ICT Services

               

              Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

               

              Tel: 01 6352710   (am only) ;

               

              Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>

                • Re: sum in load
                  Harshal Patil

                  You are using aggr function in script level so u need to mention group by at the end of table in which you are trying to use sum function.

                   

                  Put it at the end of EWTD table after Sourcepath...

                    • Re: sum in load
                      Rhona Corcoran

                      Thanks for your help will try that

                       

                       

                       

                      Rhona Corcoran

                       

                      ICT Services

                       

                      Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

                       

                      Tel: 01 6352710   (am only) ;

                       

                      Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>

                        • Re: sum in load
                          Harshal Patil

                          Rhona ,

                           

                          You can Load full EWTD table first without performing sum comment that after getting data for EWTD table ,

                          take resident load of EWTD table with new table and apply logic over their...

                           

                          NewTbl:

                           

                          TLMKey,

                          SLMKey,

                          MetricKey,

                          sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients

                          resident EWTD Group By TLMKey,SLMKey,MetricKey;


                          It will result some synthetic key..If possible do follow below approach..

                           

                          EWTD:

                          Load

                          ...

                          sql load where ...;


                          store EWTD into EWTD.qvd;

                          drop table EWTD;


                          Now Load data from EWTD.qvd


                          NewEWTD:

                          Load from Your EWTD.qvd...with adding TLMKey &' '& SLMKey &' '& MetricKey as Key field


                          NewEWTD_1:

                          Load

                          TLMKey &' '& SLMKey &' '& MetricKey as Key,

                          sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5)) ,ActualValue)) as Outpatients

                          resident EWTD Group By TLMKey,SLMKey,MetricKey;



                            • Re: sum in load
                              Rhona Corcoran

                              Sorry to take up all your time but this is what I have in now and it is

                              saying invalid expression.  When I reload the data:

                              EWTD:

                              LOAD      ActualValue,

                                        TLMKey,

                                        SLMKey,

                                  MetricDesc,

                                       MetricKey,

                                       //Acute hospital metrics

                                       if((TLMKey=18 and SLMKey=142 and MetricKey=3) or (TLMKey=18 and

                              SLMKey=142 and MetricKey=5) or (TLMKey=18 and SLMKey=142 and

                              MetricKey=4),MetricKey)  as Discharges,

                                        if(TLMKey=18 and SLMKey=166 and MetricKey=4,MetricKey) as

                              ,

                                        if((TLMKey = 18 and SLMKey = 200 and MetricKey=4) or (TLMKey =

                              18 and SLMKey = 200 and MetricKey=46),MetricKey) as [Rates of

                              Readmission],

                                        //Primary Care Metrics

                                        if(TLMKey = 10 and SLMKey = 103 and (MetricKey>=1 or MetricKey

                              <=4),MetricKey) as ,

                                        if(TLMKey=10 and SLMKey=192 and MetricKey=20,MetricKey) as

                              ,

                                        if(TLMKey = 10 and SLMKey = 191 and (MetricKey>=68 or

                              MetricKey <=79),MetricKey) as ,

                                        if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10

                              and SLMKey=101 and MetricKey=2) or (TLMKey=10 and SLMKey=101 and

                              MetricKey=11) or(TLMKey=18 and SLMKey=142 and MetricKey=12),MetricKey)

                              as ,

                                        if((TLMKey=10 and SLMKey=101 and MetricKey=1) or (TLMKey=10

                              and SLMKey=101 and MetricKey=2),MetricKey) as ,

                                        if((TLMKey=10 and SLMKey = 101 and (MetricKey>=1 or MetricKey

                              <=4) or (TLMKey=10 and SLMKey = 101 and (MetricKey>=5 or MetricKey

                              <=16))),MetricKey) as Orthodontics,

                                        RDODesc,

                                       RDOKey,

                                       ReferredMM,

                                       ReferredYYYY,

                                       SGKey,

                                        SGName,

                                        ServiceProvider,

                                        ServiceProviderType,

                                       ServiceProviderNum,

                                       if(ServiceProviderType='H',ServiceProvider) as Hospital,

                                       if(ServiceProviderType='T',ServiceProvider) as HospitalGroup,

                                       if(ServiceProviderType='O',ServiceProvider) as Team

                                       ;

                               

                               

                              SQL SELECT *

                              FROM Healthstat.dbo."MMI_ScorecardLevel3"

                              where (tlmkey = 18 and slmkey = 142 and MetricKey=3) or (tlmkey = 18 and

                              slmkey = 142 and MetricKey=4)or(tlmkey = 18 and slmkey = 142 and

                              MetricKey=5)or (tlmkey = 18 and slmkey = 166 and MetricKey=4) or (tlmkey

                              = 18 and slmkey = 200 and MetricKey=46 ) or (tlmkey = 10 and slmkey =

                              192 and MetricKey=20 )or (tlmkey = 18 and slmkey = 200 and

                              MetricKey=4)or

                              (tlmkey = 10 and slmkey = 103 and (MetricKey>=1 or MetricKey <=4))or

                              (tlmkey = 10 and slmkey = 191 and (MetricKey>=68 or MetricKey <=79))or

                              (tlmkey = 10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=2)) or

                              (tlmkey = 10 and slmkey = 101 and (MetricKey>=11 or MetricKey <=12)) or

                              (tlmkey = 10 and slmkey = 101 and MetricKey=10) or (tlmkey = 10 and

                              slmkey = 101 and MetricKey=16)

                              or (tlmkey=10 and slmkey = 101 and (MetricKey>=1 or MetricKey <=4)) or

                              (tlmkey=10 and slmkey = 101 and (MetricKey>=5 or MetricKey<=16))

                              and (ServiceProviderType='H' or ServiceProviderType='T' or

                              ServiceProviderType='O')

                               

                              ;

                               

                              Load

                               

                              ActualValue,

                               

                              TLMKey,

                               

                              SLMKey,

                               

                              MetricDesc,

                               

                              MetricKey,

                               

                              sum(if((TLMKey=18 and SLMKey=142 and Match(MetricKey,3,4,5))

                              ,ActualValue)) as Outpatients

                               

                              resident EWTD Group By TLMKey,SLMKey,MetricKey;

                               

                               

                               

                              MonthMapping:

                               

                              LOAD * INLINE [

                              ReferredMM, Month

                               

                              1, Jan

                               

                              2, Feb

                               

                              3, Mar

                               

                              4, Apr

                               

                              5, May

                               

                              6, June

                               

                              7, July

                               

                              8, Aug

                               

                              9, Sept

                               

                              10, Oct

                               

                              11, Nov

                               

                              12, Dec

                               

                              ];

                               

                               

                               

                               

                               

                              Rhona Corcoran

                               

                              ICT Services

                               

                              Health Service Executive, Dr. Steeven's Hospital, Dublin 8.

                               

                              Tel: 01 6352710   (am only) ;

                               

                              Email: rhona.corcoran@hse.ie <mailto:rhona.corcoran@hse.ie>