Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
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;