Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
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
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
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;
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 .
Does this exact same query works word to word same outside of QlikView (in Toad or whatever you use to run your query)
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) |
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);
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)';
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;