12 Replies Latest reply: Dec 22, 2016 2:36 PM by Sunny Talwar RSS

    filter on load

    Robert Winkel

      Is it possible on the load to only pull data where in the message as below to pull only messages with 'NDC from' in the message?

      LOAD ID,
          FacID,
          PatID,
          Msg,
          ChangedBy,
          ChangedOn,
          "Comment";
      SQL SELECT ID,
          FacID,
          PatID,
          Msg,
          ChangedBy,
          ChangedOn,
          "Comment"
      FROM FwReports.dbo.PatientsLog

      Where ChangedOn > '2016-12-07'
      and Msg contain 'NDC from';

       

       

       

       

         

      The following changes were made to Reorder #250207 (buPROPion TAB 150MG SR)

      NDC from "43547028950" to "00185041505"

      MOP2 from "" to ""

      Cost from "35" to "34.87"

      Fee from "-3.8" to "-3.77"

      TtlPrice from "31.2" to "31.1"

      UandC from "31.2" to "31.1"

      Payor1PaidAmt from "" to "0"

      Payor2PaidAmt from "" to "0"

      PatientPayAmt from "" to "0"

      LastModifiedBy from "sqlnlakada" to "sqlhshah"

      LastModifiedOn from "12/18/2016 10:18:19 AM" to "12/18/2016 11:12:49 AM"

      DispFor from "" to "WELLBUTRIN SR-150MG-TB12"

      DrugLabelName from "buPROPion ER-150MG-TABS" to "buPROPion TAB 150MG SR"

      TransCode2 from "" to "B1"

      QtyPerDose from "" to "2"

      LabelPrinted from "True" to "False"

      DDIChecked from "" to "False"

      PARChecked from "" to "False"

      DUPChecked from "" to "False"

      TempChange from "" to "False"

      InitReview from "12/18/2016 8:41:39 AM" to "12/18/2016 11:12:49 AM"

      AddQtyBilled from "" to "0"

      AddQtyUnbilled from "" to "0"

      IntendQty from "" to "0"

      IntendDays from "" to "0"

      DoseChecked from "" to "False"

      LabelPrintedBy from "NLAKADA" to ""

      LabelPrintedOn from "12/18/2016 10:18:18 AM" to ""

      OrigQty from "" to "0"

      OrigDaysSupply from "" to "0"

      AWP from "116.67" to "116.23"

      RebillTran from "" to "False"

      FusedTran from "" to "False"

      ThirdPartyCopay from "" to "0"
        • Re: filter on load
          Liron Baram

          LOAD ID,
              FacID,
              PatID,
              Msg,
              ChangedBy,
              ChangedOn,
              "Comment"

          where wildmatch(Msg , '*NDC from*');
          SQL SELECT ID,
              FacID,
              PatID,
              Msg,
              ChangedBy,
              ChangedOn,
              "Comment"
          FROM FwReports.dbo.PatientsLog

          Where ChangedOn > '2016-12-07'
          ;

          • Re: filter on load
            Sunny Talwar

            May be like this:

             

            LOAD ID,
                FacID,
                PatID,
                Msg,
                ChangedBy,
                ChangedOn,
                "Comment";
            SQL SELECT ID,
                FacID,
                PatID,
                Msg,
                ChangedBy,
                ChangedOn,
                "Comment"
            FROM FwReports.dbo.PatientsLog

            Where ChangedOn > '2016-12-07'
            and Msg Like 'NDC from%';

            • Re: filter on load
              Manish Kachhia

              and WildMatch(UPPER(Msg),'*NDC FROM*');

                • Re: filter on load
                  Sunny Talwar

                  Although this can be done in QlikView, I would think it would be better to restrict the un-wanted data in SQL so that the connection time to the database can be kept to minimum. What do you guys think?

                  Manish

                  Liron

                    • Re: filter on load
                      Manish Kachhia

                      Absolutely true...

                       

                      Upper(Msg) like '%NDC FROM%';

                        • Re: filter on load
                          Sunny Talwar

                          Even better

                          • Re: filter on load
                            Robert Winkel

                            I am still picking up messages that do not have NDC From

                               

                            The following changes were made to Reorder #63100 (HEPARIN SOD  INJ 10000/ML)
                               
                            Qty from "25" to "8"
                               
                            Cost from "57" to "18.24"
                               
                            Fee from "24.25" to "14.56"
                               
                            TtlPrice from "81.25" to "32.8"
                               
                            UandC from "81.25" to "32.8"
                               
                            LabQtys from "25|" to "8|"
                               
                            LastModifiedOn from "12/18/2016 10:55:11 AM" to "12/18/2016 10:55:35 AM"
                               
                            SubmitQty1 from "25" to "8"
                               
                            SubmitQty2 from "25" to "8"
                               
                            LabelPrinted from "True" to "False"
                               
                            InitReview from "12/18/2016 8:37:29 AM" to "12/18/2016 10:55:35 AM"
                               
                            LabelPrintedBy from "NLAKADA" to ""
                               
                            LabelPrintedOn from "12/18/2016 10:18:18 AM" to ""
                                AWP from "57" to "18.24"
                              • Re: filter on load
                                Sunny Talwar

                                I think it is a sql issue, have you tried running this in toad or any other tool that you use for running queries outside qlikview?

                                 

                                SELECT ID,
                                    FacID,
                                    PatID,
                                    Msg,
                                    ChangedBy,
                                    ChangedOn,
                                    "Comment"
                                FROM FwReports.dbo.PatientsLog

                                Where ChangedOn > '2016-12-07'
                                and Msg Like 'NDC from%';

                                 

                                Does it still give you those above rows?

                                • Re: filter on load
                                  Robert Winkel

                                  LOAD ID,
                                      FacID,
                                      PatID,
                                      Msg,
                                      ChangedBy,
                                      ChangedOn,
                                      "Comment";
                                  SQL SELECT ID,
                                      FacID,
                                      PatID,
                                      Msg,
                                      ChangedBy,
                                      ChangedOn,
                                      "Comment"
                                  FROM FwReports.dbo.PatientsLog

                                  Where ChangedOn > '2016-12-07'
                                  and Upper(Msg) like '%NDC FROM%';

                          • Re: filter on load
                            Robert Winkel

                            This pulls extra messages.

                             

                             

                            LOAD ID,
                                FacID,
                                PatID,
                                Msg,
                                ChangedBy,
                                ChangedOn,
                                "Comment";
                            SQL SELECT ID,
                                FacID,
                                PatID,
                                Msg,
                                ChangedBy,
                                ChangedOn,
                                "Comment"
                            FROM FwReports.dbo.PatientsLog

                            Where ChangedOn > '2016-12-07'
                            //   and Upper(Msg) like '%NDC FROM%';

                            and  Msg Like '%NDC from%';

                             

                             

                             

                            Extra message

                             

                               

                            The following changes were made to Reorder #63100 (HEPARIN SOD  INJ 10000/ML)
                               
                            Qty from "25" to "8"
                               
                            Cost from "57" to "18.24"
                               
                            Fee from "24.25" to "14.56"
                               
                            TtlPrice from "81.25" to "32.8"
                               
                            UandC from "81.25" to "32.8"
                               
                            LabQtys from "25|" to "8|"
                               
                            LastModifiedOn from "12/18/2016 10:55:11 AM" to "12/18/2016 10:55:35 AM"
                               
                            SubmitQty1 from "25" to "8"
                               
                            SubmitQty2 from "25" to "8"
                               
                            LabelPrinted from "True" to "False"
                               
                            InitReview from "12/18/2016 8:37:29 AM" to "12/18/2016 10:55:35 AM"
                               
                            LabelPrintedBy from "NLAKADA" to ""
                               
                            LabelPrintedOn from "12/18/2016 10:18:18 AM" to ""
                                AWP from "57" to "18.24"