filter on load

[#1]

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"

[lironbaram]

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'
;

[sunny_talwar · Accepted solution]

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%';

[MK_QSL]

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

[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?

mrkachhiaimp‌

lironbaram‌

[MK_QSL]

Absolutely true...

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

[sunny_talwar]

Even better

[#8]

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 Msg Like 'NDC from%';

[sunny_talwar]

Why Msg 2 times?

[#10]

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"