Delete wrong value date

[pgalvezt]

Hello, I Attached the qlikview File with field date but I can't get delete the "1899" value from Field called Year.

Could you help me with this issue please.

Thanks

[MayilVahanan · Accepted solution]

HI

Try this

LOAD
`td_mora`,
if(Year(`td_mora`) <> 1899,Year(`td_mora`)) as Year,
Month(`td_mora`) as Month,
isnull(isnum(date#(`td_mora`,'DD-MM-YYYY'))) as IsDateFlag;
SQL SELECT `td_mora`
FROM btcc11;

or

LOAD
`td_mora`,
Year(`td_mora`) as Year,
Month(`td_mora`) as Month,
isnull(isnum(date#(`td_mora`,'DD-MM-YYYY'))) as IsDateFlag;
SQL SELECT `td_mora`
FROM btcc11 where Year(`td_mora`) <> 1899;

Hope it helps

[nagaiank]

You may try this script to remove the date '30/12/1899'

LOAD

`td_mora`,

Year(`td_mora`) as Year,

Month(`td_mora`) as Month,

isnull(isnum(date#(`td_mora`,'DD-MM-YYYY'))) as IsDateFlag;

LOAD `td_mora` where `td_mora` <> '30/12/1899';

SQL SELECT `td_mora`

FROM btcc11;

[Anonymous]

With no access to your source data, I can't return a working version, however, you could try this.

Change your script to something like this...

OLEDB CONNECT TO [Provider=VFPOLEDB.1;Data Source=d:\modelos\solventa\tarjetas de credito\datos externos\bases_tc\2012\05_mayo\76 - comercial la polilla;Mode=Share Deny None;Extended Properties="";User ID="";Mask Password=False;Cache Authentication=False;Encrypt Password=False;Collating Sequence=MACHINE;DSN="";DELETED=True;CODEPAGE=1252;MVCOUNT=16384;ENGINEBEHAVIOR=90;TABLEVALIDATE=3;REFRESH=5;VARCHARMAPPING=False;ANSI=True;REPROCESS=5];

TempTable:

LOAD

`td_mora`,

Year(`td_mora`) as Year,

Month(`td_mora`) as Month,

isnull(isnum(date#(`td_mora`,'DD-MM-YYYY'))) as IsDateFlag;

SQL SELECT `td_mora`

FROM btcc11;

NoConcatenate

NewTable:

LOAD

          *

RESIDENT TempTable

WHERE Year <> 1899;

DROP TABLE TempTable;