12 Replies Latest reply: Jul 6, 2016 12:16 PM by Sunny Talwar RSS

    Where statement convert to Qlik statement?

    Robert Winkel

         

      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?

        • Re: Where statement convert to Qlik statement?
          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

          • Re: Where statement convert to Qlik statement?
            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

              • Re: Where statement convert to Qlik statement?
                Robert Winkel

                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 .

                  • Re: Where statement convert to Qlik statement?
                    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)

                      • Re: Where statement convert to Qlik statement?
                        Robert Winkel

                        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)
                          • Re: Where statement convert to Qlik statement?
                            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);

                              • Re: Where statement convert to Qlik statement?
                                Robert Winkel

                                    

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

                                 

                                  • Re: Where statement convert to Qlik statement?
                                    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;

                                      • Re: Where statement convert to Qlik statement?
                                        Robert Winkel

                                        wow that worked great.  Now the where statements, I got these to work after editing the detail.  Is there a way to copy paste?

                                         

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

                                          • Re: Where statement convert to Qlik statement?
                                            Sunny Talwar

                                            May be this:

                                             

                                            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


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

                                        • Re: Where statement convert to Qlik statement?
                                          Robert Winkel

                                          Need to run but will check back later

                                           

                                          The other times I copied the sql I had to remove Orderentrystats. before it would run  and now I cant refresh data  must be Friday

                                           

                                          Thank you for your help

                              • Re: Where statement convert to Qlik statement?
                                neetha P

                                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;