9 Replies Latest reply: Oct 8, 2018 6:08 AM by Petter Skjolden RSS

    Loading data based on date

    bhawna Agrawal

      Hi All,

       

      I have a table which has lots of data so I am trying to load data based on date else it takes lot of time in loading. I used below script to load data and added where clause but no data is loaded without any error. I tried multiple options but nothing working. Please suggest the correct syntax. ALso I tried loading date between the range of dates but that also didn't worked.

       

       

      SET DateFormat='MM/DD/YYYY'

      LOAD USR_KEY,

      ACT_KEY,

      USR_LAST_NAME,

      USR_FIRST_NAME,

      USR_MIDDLE_NAME,

      USR_DISPLAY_NAME,

      USR_LOGIN,

      USR_DISABLED_BY_PARENT,

      USR_FULL_NAME,

      USR_COUNTRY,

      USR_DEPT_NO,

      USR_DESCRIPTION,

      USR_COMMON_NAME,

      USR_EMP_NO,

      USR_FAX,

      USR_GEN_QUALIFIER,

      USR_HIRE_DATE,

      USR_HOME_PHONE,

      USR_LOCALITY_NAME,

      USR_MOBILE,

      USR_PAGER,

      USR_CREATED,

      USR_CREATE,

      USR_CREATEBY

      Where usr_create >= '10/01/2018'

       

       

      [USR]:

      SELECT *

      FROM "QA_OIM"."USR"

      Where usr_create >= '10/01/2018'

        • Re: Loading data based on date
          Giovanne Bergstein

          Hi how does the field information "usr_create" come loaded?

            • Re: Loading data based on date
              bhawna Agrawal

              usr_create is an attribute available in usr table which I am trying to load. When user is created, usr_create attribute has the value of it's creation timestamp and i do not want to load data complete table. So please suggest how can I limit the data loaded based on condition like based on creation date.

            • Re: Loading data based on date
              Petter Skjolden

              If this is an exact copy of a part of your load script it has multiple missing semicolons.

              You are missing three semicolons. One after the SET statement. One after the LOAD statement and finally one after the SELECT statement.

              • Re: Loading data based on date
                bhawna Agrawal

                Hi All,

                 

                I have seen that same issue is discussed earlier and couple of options are suggested. I tried all of them but nothing worked.

                 

                1. Using Date function in Load statement  as shown below:

                 

                Where Date(INV_DATE,'M-D-YYYY') > '1-2-2009'

                 

                2. Using make date function in Load statement as shown below


                Where Date(sold_date) >= MakeDate(2016, 1, 1);


                3. Using Set function before Load statement as shown below:


                1. SET DateFormat='M/D/YYYY';

                 

                Load

                Statements

                ..

                ..

                ..

                Where txdate >= '1/1/2010'

                ...

                ...

                SQL SELECT *

                FROM logongm.invoice


                4. Using Char function as shown below:


                LOAD USR_KEY,

                                ACT_KEY,

                                USR_LAST_NAME,

                                USR_FIRST_NAME,

                                USR_MIDDLE_NAME,

                                USR_DISPLAY_NAME,

                                USR_LOGIN,

                                USR_CREATE,

                                USR_CREATEBY ;

                  

                [USR]:

                SELECT *

                FROM "QA_OIM"."USR"

                where to_char(usr_create,'DD-MON-YY') = '04-OCT-18'

                Only the highlighted on had worked for me. For rest all options I was getting error. Also when I tried replaced the above Loading method with greater than sign it again thrown error.


                "where to_char(usr_create,'DD-MON-YY') = '04-OCT-18'"



                Please suggest the correct way to load data based on date.


                Also suggested where we have to mention the FROM and WHERE clause in the script. Whether it will come in LOAD statement of in SQL /Select statement.


                It is really confusing me and help will be appreciated.



                Thanks in Advance


                • Re: Loading data based on date
                  Petter Skjolden

                  Field names in QlikView are case-sensistive. You seem to use lower case names for the upper case named fields so that will not work in QlikView.

                   

                  You should also check whether the date fields really contain dates as opposed to being text fields that look like they contain dates. You can do that in the Table Viewer or in the Document Properties in the Table tab:

                   

                  2018-10-07 16_30_11-Microsoft Edge.png

                   

                  2018-10-07 16_31_37-Microsoft Edge.png

                   

                  Notice that fields that have "real" dates in them has the tags $timestamp and $date