Problem with date fields while applying Incremental Load

[gizzel123]

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.

[qlikmsg4u]

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

[Peter_Cammaert]

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

[gizzel123]

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.

[qlikmsg4u]

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.

[jonathandienst]

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')

[gizzel123]

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

Please help.

[qlikmsg4u]

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)';

[gizzel123]

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