Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

SQL select by year

hi, i have a small problem

usually, the whole oracle DB is loaded in a QVD container.

Now, data should be loaded incrementally, so the range of time to be loaded, has to be limited.

DBconsum:

LOAD *;

SQL SELECT * FROM MYDB."consumption_s"

WHERE year(TIME)= 2012 and month(TIME) = 5

;

STORE DBconsum INTO $(vQVDStore)consumption_s_inc.QVD;

usually, that would be no big deal, its very basic sql.

the timestamp (TIME) has the format "41091,083333333" , which is usually readable by qlikview as "01.07.2012 02:00:00" or just "2012".

but it doesnt seem to work with this sql load.

any suggestions ?

7 Replies
etl_tools
Contributor

Re: SQL select by year

Do you really need a time part?

May be you can just remove it?

Mike

Not applicable

Re: SQL select by year

etl_tools wrote:

Do you really need a time part?

May be you can just remove it?

Mike

hi mike, im not really understanding your reply. what do you mean by "remove" ?

the TIME field is needet, otherwise, the data makes no sense.

how else is it possible to select, which months to load ?

etl_tools
Contributor

Re: SQL select by year

01.07.2012 02:00:00

01.07.2012 <= Date part

02:00:00    <= Time Part (Do you use it in your dashboard?)

Which database do you work with?

Is it Oracle, MySQL ETC

Mike


Not applicable

Re: SQL select by year

an oracle DB is used.

all parts of the timestamp are used in the dashboard, but that doesnt matter during load.

i have to load the TIME field (content= "41091,083333333"), get the year, month, and load only record from a certain month in 2012.

Re: SQL select by year

Hi

Try like this

DBconsum:

LOAD * WHERE year(TIME)= 2012 and month(TIME) = 'May';

SQL SELECT * FROM MYDB."consumption_s";

etl_tools
Contributor

Re: SQL select by year

As a test can you try the following SQL to see if it works

DBconsum:

LOAD *;

SQL SELECTcast(TIME AS varchar2(40) ) as TIME

FROM MYDB."consumption_s"

WHERE year(TIME)= 2012 and month(TIME) = 5;

STORE DBconsum INTO $(vQVDStore)consumption_s_inc.QVD;

Mike

QlikView connector which works with 28 datasources and has 300 + validation functions

http://www.etl-tools.com/etl-tools/qlikview-connector/overview.html


etl_tools
Contributor

Re: SQL select by year

Alexander

I probably misunderstood you.

Are you saing that when you try to load the data the script fails with error?

Or the sql does not return any data?

Mike