Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
pgalvezt
Specialist
Specialist

Delete wrong value date

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

1 Solution

Accepted Solutions
MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

3 Replies
MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

nagaiank
Specialist III
Specialist III

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;

aaronchandlerwo
Contributor III
Contributor III

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;