Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

filter on load

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"
Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: filter on load

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

12 Replies
lironbaram
Not applicable

Re: filter on load

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
Not applicable

Re: filter on load

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
Not applicable

Re: filter on load

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

sunny_talwar
Not applicable

Re: filter on load

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
Not applicable

Re: filter on load

Absolutely true...

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

sunny_talwar
Not applicable

Re: filter on load

Even better

Not applicable

Re: filter on load

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
Not applicable

Re: filter on load

Why Msg 2 times?

Capture.PNG

Not applicable

Re: filter on load

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"