7 Replies Latest reply: Dec 16, 2016 3:46 PM by Sunny Talwar RSS

    Script Help Please

    Mark Ritter

      I am attempting to load the max value of a field and put it into a variable.

       

      For some reason I am not getting any value loaded.  Here is the script that I am running.  I am clearly doing something wrong.

       

      Outcomes:

      LOAD

          CUSTOMERID

      Resident Customers;

       

      Left Join

      LOAD

          CustomerID & DischargeYear & '-' & DischargeMonth as Key,

          //Division,

          //Region,

          //District,

          //Chain,

          Facility,

          CaseNumber,

          Payor,

          //PatientName,

          MeasureDesc,

          SRCategory,

          Discipline, 

          MeasureGroup_Scale,

          MeasureValueStart,

          MeasureValueEnd,

          //Gain,

          //PreMorbid,

          //Goal,

          //Interim,

         // MetGoal,

          //BackPreMorbid,

          LOS,

          CustomerID as CUSTOMERID,

          GroupDesc,

          Category,

          ValueDesc,

          EOCDest,

          EOCDestFilter,

          PatientID,

          DischargeDate,

          SecondaryProgramDesc,

          DischargeMonth,

          DischargeYear,

          Discharge_Year_Month,

          Load_Date,

          VECTR,

          IN_PAT,

          ADM_TYP,

          ADM_DT,

          ADM_MNTH,

          ADM_YR,

          ADM_YR_MNTH,

          DSCHRG_YR_MNTH,

          PRGM_CNSUS_DSCHRG

         

      FROM [lib://QVD Files/Healthmax/Outcomes.QVD] (qvd);

       

       

      tmp:

      Load Max(Discharge_Year_Month) As max_Discharge_Period

      Resident Outcomes;

       

      Let vMaxDate=Peek('max_Discharge_Period');

       

      //Drop Table tmp;

        • Re: Script Help Please
          Sunny Talwar

          Since you are doing a Left Join from your second table, does any of the CustomerID match between the two tables? Because is no CustomerID match, the table that you are joining from will not bring anything.

            • Re: Script Help Please
              Mark Ritter

              I just added the tmp table portion.  The rest of the script works fine and returns the expected data.

                • Re: Script Help Please
                  Sunny Talwar

                  Then you need to make sure that Discharge_Year_Month is read as a dual field within QlikView. Read about dates in QlikView here:

                  Why don’t my dates work?

                  Get the Dates Right

                    • Re: Script Help Please
                      Mark Ritter

                      It is actually not a date.  It is a field that combines just the year and month.  Like 2016-9.

                        • Re: Script Help Please
                          Sunny Talwar

                          May be try like this:

                           

                          Outcomes:

                          LOAD

                              CUSTOMERID

                          Resident Customers;

                           

                          Left Join

                          LOAD

                              CustomerID & DischargeYear & '-' & DischargeMonth as Key,

                              //Division,

                              //Region,

                              //District,

                              //Chain,

                              Facility,

                              CaseNumber,

                              Payor,

                              //PatientName,

                              MeasureDesc,

                              SRCategory,

                              Discipline,

                              MeasureGroup_Scale,

                              MeasureValueStart,

                              MeasureValueEnd,

                              //Gain,

                              //PreMorbid,

                              //Goal,

                              //Interim,

                             // MetGoal,

                              //BackPreMorbid,

                              LOS,

                              CustomerID as CUSTOMERID,

                              GroupDesc,

                              Category,

                              ValueDesc,

                              EOCDest,

                              EOCDestFilter,

                              PatientID,

                              DischargeDate,

                              SecondaryProgramDesc,

                              DischargeMonth,

                              DischargeYear,

                             Date(Date#(Discharge_Year_Month, 'YYYY-M'), 'YYYY-M') as Discharge_Year_Month,

                              Load_Date,

                              VECTR,

                              IN_PAT,

                              ADM_TYP,

                              ADM_DT,

                              ADM_MNTH,

                              ADM_YR,

                              ADM_YR_MNTH,

                              DSCHRG_YR_MNTH,

                              PRGM_CNSUS_DSCHRG

                            

                          FROM [lib://QVD Files/Healthmax/Outcomes.QVD] (qvd);

                           

                           

                          tmp:

                          Load Max(Discharge_Year_Month) As max_Discharge_Period

                          Resident Outcomes;

                           

                          Let vMaxDate=Peek('max_Discharge_Period');

                           

                          //Drop Table tmp;