Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Where statement convert to Qlik statement?

   

WHERE (OrderEntryStats.Entered>={ts '2015-12-01 00:00:00'}) AND (OrderEntryStats.FacId Not In ('FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')) AND (OrderEntryStats.Deleted=1)

How do I convert this to work in the data load?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hi  Robert,

Try this:

LOAD *
WHERE OrderEntryStats.Entered >= '2015-12-01 00:00:00' AND  NOT MATCH(OrderEntryStats.FacId,'FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')  AND  OrderEntryStats.Deleted =  1;

View solution in original post

12 Replies
marcus_sommer

Try this:

WHERE

OrderEntryStats.Entered >= '2015-12-01 00:00:00' AND

match(OrderEntryStats.FacId,                     'FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag') = 0 AND OrderEntryStats.Deleted=1;

whereby OrderEntryStats.Entered must be interpreted as timestamp which might need some transformation with converting/formating functions like date, date#, floor, frac, timestamp# ... in beforehand.

- Marcus

sunny_talwar

Are you planning to move this Where statement from SQL into QlikView? If you are I would like you to reconsider. The reason I say this is because by moving the where statement into QlikView you will need to pull all the records from the SQL datasource and then eventually once they are here you will drop some of them through your Where clause. If you don't need them, stop them in SQL itself so that it takes less time for the SQL to give you only those rows which you actually need.

Best,

Sunny

Anonymous
Not applicable
Author

Hi  Robert,

Try this:

LOAD *
WHERE OrderEntryStats.Entered >= '2015-12-01 00:00:00' AND  NOT MATCH(OrderEntryStats.FacId,'FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')  AND  OrderEntryStats.Deleted =  1;

Not applicable
Author

I thought that's how I was doing it?

Or at one point I tried to link the SQL but never got it to work so I am trying the direct connect?

I originally started with Excel file created with Microsoft qry but you have to refresh all the time wanting to schedule and just let it run .

sunny_talwar

Does this exact same query works word to word same outside of QlikView (in Toad or whatever you use to run your query)

Not applicable
Author

Not really  below is copy of qry  I tried just pasting it into Qlik no luck

   

SELECT OrderEntryStats.EnteredByNPI AS 'OEDEnteredByNPI', OrderEntryStats.RxNo, OrderEntryStats.Entered AS 'OEDEntered', OrderEntryStats.PhrNPI AS 'OEDPhrNPI', OrderEntryStats.InitReview AS 'OEDInitReview', OrderEntryStats.ProfileOnly AS 'OEDProfileOnly', OrderEntryStats.DispenseDt AS 'OEDDispenseDt', OrderEntryStats.TransType AS 'OEDTransType', OrderEntryStats.Deleted AS 'OEDDeleted', OrderEntryStats.FacId AS 'OEDFacID', OrderEntryStats.PatId AS 'OEDPatID'
FROM Rx.dbo.OrderEntryStats OrderEntryStats
WHERE (OrderEntryStats.Entered>={ts '2015-12-01 00:00:00'}) AND (OrderEntryStats.FacId Not In ('FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')) AND (OrderEntryStats.Deleted=1)
sunny_talwar

So this works in SQL works outside of QlikView?

SELECT

OrderEntryStats.EnteredByNPI AS 'OEDEnteredByNPI',

OrderEntryStats.RxNo,

OrderEntryStats.Entered AS 'OEDEntered',

OrderEntryStats.PhrNPI AS 'OEDPhrNPI',

OrderEntryStats.InitReview AS 'OEDInitReview',

OrderEntryStats.ProfileOnly AS 'OEDProfileOnly',

OrderEntryStats.DispenseDt AS 'OEDDispenseDt',

OrderEntryStats.TransType AS 'OEDTransType',

OrderEntryStats.Deleted AS 'OEDDeleted',

OrderEntryStats.FacId AS 'OEDFacID',

OrderEntryStats.PatId AS 'OEDPatID'


FROM Rx.dbo.OrderEntryStats OrderEntryStats


WHERE (OrderEntryStats.Entered>={ts '2015-12-01 00:00:00'}) AND (OrderEntryStats.FacId Not In ('FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')) AND (OrderEntryStats.Deleted=1);


Can you try this in QlikView:

TableName:

LOAD *;

SQL SELECT

OrderEntryStats.EnteredByNPI AS 'OEDEnteredByNPI',

OrderEntryStats.RxNo,

OrderEntryStats.Entered AS 'OEDEntered',

OrderEntryStats.PhrNPI AS 'OEDPhrNPI',

OrderEntryStats.InitReview AS 'OEDInitReview',

OrderEntryStats.ProfileOnly AS 'OEDProfileOnly',

OrderEntryStats.DispenseDt AS 'OEDDispenseDt',

OrderEntryStats.TransType AS 'OEDTransType',

OrderEntryStats.Deleted AS 'OEDDeleted',

OrderEntryStats.FacId AS 'OEDFacID',

OrderEntryStats.PatId AS 'OEDPatID'


FROM Rx.dbo.OrderEntryStats OrderEntryStats


WHERE (OrderEntryStats.Entered>={ts '2015-12-01 00:00:00'}) AND (OrderEntryStats.FacId Not In ('FORMS','MSTR','RETAIL','SAMPLE','TEST','TEST#2','TEST2','TEST3','TRLK','stag')) AND (OrderEntryStats.Deleted=1);

Not applicable
Author

    

TableName: Rx.dbo.OrderEntryStats OrderEntryStats

LOAD *;

SQL SELECT

OrderEntryStats.EnteredByNPI AS 'OEDEnteredByNPI',
OrderEntryStats.RxNo,
OrderEntryStats.Entered AS 'OEDEntered',
OrderEntryStats.PhrNPI AS 'OEDPhrNPI',
OrderEntryStats.InitReview AS 'OEDInitReview',
OrderEntryStats.ProfileOnly AS 'OEDProfileOnly',
OrderEntryStats.DispenseDt AS 'OEDDispenseDt',
OrderEntryStats.TransType AS 'OEDTransType',
OrderEntryStats.Deleted AS 'OEDDeleted',
OrderEntryStats.FacId AS 'OEDFacID',
OrderEntryStats.PatId AS 'OEDPatID'

FROM Rx.dbo.OrderEntryStats OrderEntryStats

  
LIB CONNECT TO 'pmd-sandbox (pharmore_rwinkel)';

sunny_talwar

You don't need this:

TableName: Rx.dbo.OrderEntryStats OrderEntryStats

Just try this (paste as is and see what you get)

LIB CONNECT TO 'pmd-sandbox (pharmore_rwinkel)';

Table1:

LOAD *;

SQL SELECT

OrderEntryStats.EnteredByNPI AS 'OEDEnteredByNPI',
OrderEntryStats.RxNo,
OrderEntryStats.Entered AS 'OEDEntered',
OrderEntryStats.PhrNPI AS 'OEDPhrNPI',
OrderEntryStats.InitReview AS 'OEDInitReview',
OrderEntryStats.ProfileOnly AS 'OEDProfileOnly',
OrderEntryStats.DispenseDt AS 'OEDDispenseDt',
OrderEntryStats.TransType AS 'OEDTransType',
OrderEntryStats.Deleted AS 'OEDDeleted',
OrderEntryStats.FacId AS 'OEDFacID',
OrderEntryStats.PatId AS 'OEDPatID'

FROM Rx.dbo.OrderEntryStats OrderEntryStats;