Loading data based on date

[Anonymous]

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'

[giovanneb]

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

[petter]

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.

[Anonymous]

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.

[Anonymous]

Thanks for your response. I tried by putting semicolon but now it is throwing an error while loading data.

[Anonymous]

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

[vijayaganesh_s]

Give try using WHERE MATCH() function

[petter]

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:

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

[Anonymous]

Thanks Petter. I got this issue resolved.

[petter]

Great - then please close the thread by marking the answer that was correct as "correct answer"