Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
neetha_p
Honored Contributor

Re: Where statement convert to Qlik statement?

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;

12 Replies

Re: Where statement convert to Qlik statement?

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

MVP
MVP

Re: Where statement convert to Qlik statement?

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

neetha_p
Honored Contributor

Re: Where statement convert to Qlik statement?

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

Re: Where statement convert to Qlik statement?

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 .

MVP
MVP

Re: Where statement convert to Qlik statement?

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

Re: Where statement convert to Qlik statement?

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)
MVP
MVP

Re: Where statement convert to Qlik statement?

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

Re: Where statement convert to Qlik statement?

    

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

MVP
MVP

Re: Where statement convert to Qlik statement?

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;