topic Re: Problem with date fields while applying Incremental Load in QlikView

Problem with date fields while applying Incremental Load

gizzel123 — Fri, 05 Jun 2015 06:55:17 GMT

hi All,

I have to apply Insert and update incremental load.

But I have been facing issue with date fields.

table1:

Load

Date(Max(UPDATE_DATE)) as Max_UPDATE_DATE

FROM

$(QVDPath)table1.qvd

(qvd);

Let vUPDATE_DATE=Floor(peek('Max_UPDATE_DATE',0,'table1'));

PO_LINES_DET:

LOAD * ;

SQL SELECT *

FROM table1 WHERE UPDATE_DATE>$(vUPDATE_DATE);

But here the variable is storing date as number,so I tried converting it to date by TO_DATE('$(vUPDATE_DATE)','MM-DD-YYYY HH24:MI:SS');

but I am getting error.also many of the oracle functions like convert(), converting into number is not working.

please advise.I am stuck.

thanks in advance.

Re: Problem with date fields while applying Incremental Load

qlikmsg4u — Fri, 05 Jun 2015 07:26:51 GMT

Hi Gizzel,

try to convert the date number in variable itself.

please post sample application, so that it will be easy for us to understand.

Regards

Re: Problem with date fields while applying Incremental Load

Peter_Cammaert — Fri, 05 Jun 2015 07:33:03 GMT

What QLIKmsg4u is saying is to try to convert the date value in QlikView Script instead of in SQL. for example this could work:

Let vUPDATE_DATE=Date(Floor(peek('Max_UPDATE_DATE',0,'table1')),'MM-DD-YYYY');

Best,

Peter

Re: Problem with date fields while applying Incremental Load

gizzel123 — Fri, 05 Jun 2015 08:43:29 GMT

Okay now I have variable in date format...but the UPDATE_DATE is in Date time.

sql select * from table1 where to_date(vUPDATE_DATE,'MM-DD-YYYY')>$(vUPDATE_DATE) is giving me error : NOT A VALID MONTH.

Re: Problem with date fields while applying Incremental Load

qlikmsg4u — Fri, 05 Jun 2015 08:58:53 GMT

Hi Gizzel,

try your syntax like this :

sql select * from table1 where "Your Date Column" > $(vUPDATE_DATE)

and if you want timestamps in vUPDATE_DATE remove Floor.

Re: Problem with date fields while applying Incremental Load

jonathandienst — Fri, 05 Jun 2015 09:06:50 GMT

You have a stray "v" prefix on your field name. Do you need the to_date()? An you will need to quote the literal date.

Create the variable as described above by Peter and then try this:

sql select * from table1 where UPDATE_DATE > '$(vUPDATE_DATE)'

or this:

sql select * from table1 where UPDATE_DATE > to_date('$(vUPDATE_DATE)', 'MM-D-YYYY')

Re: Problem with date fields while applying Incremental Load

gizzel123 — Fri, 05 Jun 2015 09:11:01 GMT

No I want my "Date Column" ie UPDATE_DATE into MM-DD-YYYY format;

Please help.

Re: Problem with date fields while applying Incremental Load

qlikmsg4u — Fri, 05 Jun 2015 09:22:37 GMT

Hi GIzzel,

try this solution this may work,

Let vUPDATE_DATE=Date(Floor(peek('Max_UPDATE_DATE',0,'table1')),'DD-MMM-YYYY');

sql select * from table1 where UPDATE_DATE > '$(vUPDATE_DATE)';

Re: Problem with date fields while applying Incremental Load

gizzel123 — Fri, 05 Jun 2015 09:58:41 GMT

But here UPDATE_DATE is in DATE-time format ..and to_date(UPDATE_DATE,'MM-DD-YYYY') is not working .