Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
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
Highlighted
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;

View solution in original post

12 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted

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

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

View solution in original post

Highlighted
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 .

Highlighted

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)

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

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

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

Highlighted

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;