Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

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;

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;