Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
HI
Try like this
if(Right([HMDTENT],2) <> 00, Date(Date#([HMDTENT],'YYYYMMDD'),'DD/MM/YYYY'),'')
Hope it helps
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?
Hi
Can you elaborate your error?
Can you able to send a sample data for work out it?
I have fogot to load 'HMDTENT'
HMDTENT,
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 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 80 20130228 20130220
A N C 116 20130228 20130128
A N C 116 20130300 20130128
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,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,80,20130228,20130220
A N C,116,20130228,20130128
A N C,116,20130300,20130128
];
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
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,
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 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 80 20130228 20130220 28/02/2013 20/02/2013
A N C 116 20130228 20130128 28/02/2013 28/01/2013
A N C 116 20130300 20130128 28/01/2013
Is not possible to suppress the row if empty?
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
.png "Former Employee"){kind=icon}