Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhawna_agrawal
Contributor II
Contributor II

Loading data based on date

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'

9 Replies
giovanneb
Creator II
Creator II

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

petter
MVP
MVP

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.

bhawna_agrawal
Contributor II
Contributor II
Author

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.

bhawna_agrawal
Contributor II
Contributor II
Author

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

bhawna_agrawal
Contributor II
Contributor II
Author

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
Partner
Partner

Give try using WHERE MATCH() function

petter
MVP
MVP

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

bhawna_agrawal
Contributor II
Contributor II
Author

Thanks Petter. I got this issue resolved.

petter
MVP
MVP

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