11 Replies Latest reply: May 7, 2016 9:06 AM by Stefan Wühl RSS

    Selected Data Pull

    Jeffory Frayser

      All,

       

      I have had such helpful responses so I'll try another.  I would like to restrict the amount of data pulled from my SQL database to only the 5 days prior to when I load.  One field in my data is ORDDATE so I would like to pull the latest 5 days.

       

      I think I would use something like Where ORDDATE > Today()-5 or such.

       

      Any suggestions?

        • Re: Selected Data Pull
          Stefan Wühl

          I assume the WHERE clause is part of your SQL. Probably your SQL server don't understand Today(), but you can use a variable:

           

          Let vThreshold = Num(Today()-5);

           

           

           

          SELECT ....

          FROM ...

          WHERE ORDDATE > $(vThreshold);

            • Re: Selected Data Pull
              Sunny Talwar

              or may be this:

               

              LET vThreshold = Date(Today()-5, 'DD-MMM-YYYY');

               

               

               

              SELECT ....

              FROM ...

              WHERE ORDDATE > '$(vThreshold)';

               

              UPDATE: Missed one M before

                • Re: Selected Data Pull
                  Jeffory Frayser

                  When I am getting the following:

                   

                  The following error occurred:

                  ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Error converting data type varchar to numeric.

                   

                  Let vThreshold = Date(Today()-5,'MM/DD/YYYY');

                  LIB CONNECT TO 'svtrucosql03';

                  LOAD ORDUNIQ,
                       Date(Date#(ORDDATE, 'YYYYMMDD'), 'MM/DD/YYYY') as ORDDATE,
                  //      ORDDATE,
                       SHIPDATE,
                       INVDATE,
                       INVWEIGHT;
                  SQL SELECT ORDUNIQ,
                       ORDDATE,
                       SHIPDATE,
                       INVDATE,
                       INVWEIGHT
                  FROM TRUDAT.dbo.OEORDH Where ORDDATE > '$vThreshold';

                   

                  Suggestions?

                    • Re: Selected Data Pull
                      Sunny Talwar

                      I think you missed the parenthesis


                      Where ORDDATE > '$(vThreshold)';

                        • Re: Selected Data Pull
                          Sunny Talwar

                          And may be this format for date:


                          Let vThreshold = Date(Today()-5,'DD-MMM-YYYY');

                            • Re: Selected Data Pull
                              Jeffory Frayser

                              Worked and got it.  Thanks

                               

                              Do I need to do something so you get points for being so helpful?

                                • Re: Selected Data Pull
                                  Sunny Talwar

                                  Awesome -

                                   

                                  Not just because it gives me point, but because this might help someone else in the future if they have a similar issue and might find it easy to browse this discussion if you close the thread by marking correct and helpful responses.

                                  Qlik Community Tip: Marking Replies as Correct or Helpful

                                   

                                  Best,

                                  Sunny

                                    • Re: Selected Data Pull
                                      Jeffory Frayser

                                      Why do you think it didn't pull just a subset of my data?  It appears as though the whole data set was pulled even though the where clause should have only take the portion within the 5 days. 

                                        • Re: Selected Data Pull
                                          Stefan Wühl

                                          Probably because you are comparing apple to oranges in the WHERE clause.

                                           

                                          You would need to find a format for the variable that the DBMS can compare to the ORDDATE values in your DB table. Maybe just ask your local DB guru how this value needs to look like.

                                          • Re: Selected Data Pull
                                            Sunny Talwar

                                            I agree with Stefan that the where clause might not have worked because of which it pulled all the data. I shared the date format (DD-MMM-YYYY) based on my experience with Oracle. This seem to have worked with it, but depending on what database is, the where statement might need another kind of format.

                                             

                                            If you have run the same query, what format would have used for the Where clause? if you can figure that out then you can format your variable in this way

                                             

                                            DD or dd -> for date formatted 01, 02, 03

                                            D or d -> for date formatted 1, 2, 3

                                            MM -> for Month 01, 02, 03 (don't use mm because it is used for minutes in QlikView)

                                            M -> for Month formatted 1, 2, 3

                                            MMM -> for 3 letter month name Jan, Feb (NOTE: This depend on your environmental settings and might give you different result based on what is used in the set statement)

                                            Capture.PNG

                                            MMMM -> for the complete month name January, February (NOTE: again based on environmental variable)

                                            Capture.PNG

                                            YY or yy -> for two digit year 15, 16

                                            YYYY or yyyy -> for four digit years 2015,2016

                                             

                                            And then different ways to join them using - or / or .

                                            Examples - > DD.MM.YYYY, M.D.YYYY, DD/MMM/YYYY, YY-MM-DD

                                             

                                            So once you figure out the format, use that to create your variable and send it to the where statement.

                                             

                                            I hope this will help you.

                                             

                                            Best,

                                            Sunny

                                              • Re: Selected Data Pull
                                                Stefan Wühl

                                                You can find the format codes definitions e.g. here

                                                Conventions for number and time formats ‒ QlikView

                                                On Format Codes for Numbers and Dates

                                                 

                                                 

                                                Again, the basic principle is that the SQL part including the WHERE clause is sent to the SQL driver as is and executed by the driver. So you need to create a code that is working with your DBMS.

                                                 

                                                The only thing here where Qlik plays a role is substituting the Qlik variable to text using dollar sign expansion.

                                                 

                                                Having said this, assuming you are using MS SQL server, you could also try something like

                                                 

                                                Let vThreshold = Date(Today()-5,'YYYY-MM-DD');


                                                SQL SELECT ORDUNIQ,

                                                     ORDDATE,

                                                     SHIPDATE,

                                                     INVDATE,

                                                     INVWEIGHT

                                                FROM TRUDAT.dbo.OEORDH WHERE ORDDATE > {d '$(vThreshold)'};



                                                But again, if you are not familiar with your DBMS and you want to use SQL, ask your local SQL developer to help you with the syntax or variable format.