topic Re: Suppress false date in QlikView

Suppress false date

realpixel — Fri, 29 Mar 2013 17:49:41 GMT

Hello,

I have the following script to convert the value to a date format

Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY') as [HMDTENT],

There are some value with date erroneous like '20130300' (Day is 00), How to suppress the row if the date is false?

Re: Suppress false date

MayilVahanan — Fri, 29 Mar 2013 18:05:00 GMT

Try like this

if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY'),'')

Hope it helps

Re: Suppress false date

realpixel — Fri, 29 Mar 2013 18:14:57 GMT

Hi,

Thank you for reply

I try

LOAD DXSHIPO,

HMDFACT,

HMDISPO,

if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY'),''),

HMDTSAI,

HMESCALE,

HMESCDBQ;

But there is mistake maybe because, I not placed this function in the right place?

Re: Suppress false date

MayilVahanan — Fri, 29 Mar 2013 18:31:10 GMT

Can you elaborate your error?

Can you able to send a sample data for work out it?

Re: Suppress false date

realpixel — Fri, 29 Mar 2013 20:57:02 GMT

I have fogot to load 'HMDTENT'

LOAD DXSHIPO,

HMDFACT,

HMDISPO,

HMDTENT,

if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY'),''),

HMDTSAI,

HMESCALE,

HMESCDBQ;

Now no error but no modification, HMDTENT is not formated at the date format (DD/MM/YYYY) and there are always some value like 20130300, see under the result.

HMPOSIT HMTRANSI HMDTENT HMDATE

A N C 116 20130216 20130212

A N C 116 20130130 20130122

A N C 116 20130224 20130220

A N C 116 20130122 20130108

A N C 116 20130228 20130224

A N C 80 20130108 20121208

A N C 116 20130124 20130120

A N C 116 20130128 20130116

A N C 116 20130132 20130116

A N C 116 20130124 20130120

A N C 116 20130110 20121228

A N C 116 20130320 20130310

A N C 80 20130108 20121228

A N C 116 20130116 20130112

A N C 116 20130110 20121226

A N C 116 20130110 20121216

A N C 116 20130200 20130130

A N C 116 20130114 20130108

A N C 116 20130320 20130316

A N C 116 20130200 20130130

A N C 80 20130228 20130220

A N C 116 20130124 20130120

A N C 116 20130228 20130128

A N C 116 20130300 20130128

A N C 116 20130228 20130224

Re: Suppress false date

MayilVahanan — Fri, 29 Mar 2013 21:13:28 GMT

Try like this

Load *, if(Right(HMDTENT,2)<>00,Date(Date#(HMDTENT,'YYYYMMDD'),'DD/MM/YYYY'),'') AS Test;

Load * Inline

[

HMPOSIT,HMTRANSI,HMDTENT,HMDATE

A N C,116,20130216,20130212

A N C,116,20130130,20130122

A N C,116,20130224,20130220

A N C,116,20130122,20130108

A N C,116,20130228,20130224

A N C,80,20130108,20121208

A N C,116,20130124,20130120

A N C,116,20130128,20130116

A N C,116,20130132,20130116

A N C,116,20130124,20130120

A N C,116,20130110,20121228

A N C,116,20130320,20130310

A N C,80,20130108,20121228

A N C,116,20130116,20130112

A N C,116,20130110,20121226

A N C,116,20130110,20121216

A N C,116,20130200,20130130

A N C,116,20130114,20130108

A N C,116,20130320,20130316

A N C,116,20130200,20130130

A N C,80,20130228,20130220

A N C,116,20130124,20130120

A N C,116,20130228,20130128

A N C,116,20130300,20130128

A N C,116,20130228,20130224

];

Hope it helps

Re: Suppress false date

Fri, 29 Mar 2013 21:23:13 GMT

Hi all,

if you mean with supressing the according rows not to load them, use

LOAD . .. . . .

if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY')) AS MyDate

HtH

Roland

Re: Suppress false date

realpixel — Fri, 29 Mar 2013 22:01:08 GMT

Thant you very mutch for your reply

I have replaced 'MyDate' by HMDTENT1 and the same function for HMDTENT

HMDATE,

if(Right([HMDATE],2) <> 00, Date(Date#([HMDATE],'YYYYMMDD'),'DD/MM/YYYY')) as HMDATE1,

HMDAU,

HMDDPREN,

HMDERM,

HMDFACT,

HMDISPO,

HMDTENT,

if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY')) as HMDTENT,

see under the result

HMPOSIT HMTRANSI HMDTENT HMDATE HMDATE1 HMDTENT1

A N C 116 20130216 20130212 16/02/2013 12/02/2013

A N C 116 20130130 20130122 30/01/2013 22/01/2013

A N C 116 20130224 20130220 24/02/2013 20/02/2013

A N C 116 20130122 20130108 22/01/2013 08/01/2013

A N C 116 20130228 20130224 28/02/2013 24/02/2013

A N C 80 20130108 20121208 08/01/2013 08/12/2012

A N C 116 20130124 20130120 24/01/2013 20/01/2013

A N C 116 20130128 20130116 28/01/2013 16/01/2013

A N C 116 20130132 20130116 16/01/2013

A N C 116 20130124 20130120 24/01/2013 20/01/2013

A N C 116 20130110 20121228 10/01/2013 28/12/2012

A N C 116 20130320 20130310 20/03/2013 10/03/2013

A N C 80 20130108 20121228 08/01/2013 28/12/2012

A N C 116 20130116 20130112 16/01/2013 12/01/2013

A N C 116 20130110 20121226 10/01/2013 26/12/2012

A N C 116 20130110 20121216 10/01/2013 16/12/2012

A N C 116 20130200 20130130 30/01/2013

A N C 116 20130114 20130108 14/01/2013 08/01/2013

A N C 116 20130320 20130316 20/03/2013 16/03/2013

A N C 116 20130200 20130130 30/01/2013

A N C 80 20130228 20130220 28/02/2013 20/02/2013

A N C 116 20130124 20130120 24/01/2013 20/01/2013

A N C 116 20130228 20130128 28/02/2013 28/01/2013

A N C 116 20130300 20130128 28/01/2013

A N C 116 20130228 20130224 28/02/2013 24/02/2013

Is not possible to suppress the row if empty?

Re: Suppress false date

hic — Sat, 30 Mar 2013 21:42:43 GMT

Most erronous dates can be found using

IsNum(Date#(HMDTENT,'YYYYMMDD'))

which evaluates to true if the Date can be interpreted and false if DD is 00 or 32 or higher.

HIC