topic Re: SQL select by year in QlikView

SQL select by year

Fri, 02 Nov 2012 11:32:27 GMT

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 ?

Re: SQL select by year

etl_tools — Fri, 02 Nov 2012 11:47:36 GMT

Do you really need a time part?

May be you can just remove it?

Mike

Re: SQL select by year

Fri, 02 Nov 2012 11:56:14 GMT

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 ?

Re: SQL select by year

etl_tools — Fri, 02 Nov 2012 12:03:45 GMT

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

Re: SQL select by year

Fri, 02 Nov 2012 12:17:42 GMT

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

MayilVahanan — Fri, 02 Nov 2012 12:56:59 GMT

Try like this

DBconsum:

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

SQL SELECT * FROM MYDB."consumption_s";

Re: SQL select by year

etl_tools — Fri, 02 Nov 2012 13:02:33 GMT

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

Re: SQL select by year

etl_tools — Fri, 02 Nov 2012 15:06:03 GMT

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