Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
My user have accidentally enter date 14/12/2030 into GL Date . The correct date should be 14/12/2014. ( Now my Year field have year from 2020 till 2030 which cause the major problem )
May i know how can i using Mapping to recode the date ?
Map_Date:
LOAD GL_CODE,
GL_DATE,
AMOUNT,
GL_DATE1
FROM
MapGL_PENANG.xlsx
(ooxml, embedded labels, table is TDSS);
Mapping_table :-
GL_CODE | GL_DATE | AMOUNT |
90511 | 14/12/2014 | 113 |
My idea is when GL_CODE = 90511 and Amount = 113.00 Replace GL_Date to 14/12/2014 , My script :-
ApplyMap('Map_Date',@1:16T,@50:52T) AS GL_DATE_NEW,
GL_TABLE:
LOAD
'TDSS' as SOURCE,
@1:16T as GL_CODE_,
date#(@58:68T,'DD/MM/YYYY') as GL_Date_,
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount],
ApplyMap('Map_Date',@1:16T,@50:52T) AS GL_DATE_NEW,
My question is above highlight red color field it is correct ?
Maybe try using an If statement instead of Map
If(( GL_CODE=90511 and AMOUNT=113), '14/12/2014', date#(@58:68T,'DD/MM/YYYY')) as GL_DATE_NEW
Maybe try using an If statement instead of Map
If(( GL_CODE=90511 and AMOUNT=113), '14/12/2014', date#(@58:68T,'DD/MM/YYYY')) as GL_DATE_NEW
However, you can also use a mapping table if you have multiple replacements
Like
Map_Table:
Mapping Load
'90511'&'112' as Key
'14/12/2014' as Value
Autogenerate(1);
fact:
Load *,
Applymap('Map_Table', GL_CODE&AMOUNT, date#(@58:68T, 'DD/MM/YYYY')) as GL_DATE_NEW
May be first you should pull all invalid dates. For example any date which is greater than Today, will give some sort of invalid date. I think you have found only one case here 14/12/2030. There might be more sometimes.
HI Sir
Thank you very much , i try and get error code :-
Syntax error, missing/misplaced FROM:
Map_Table:
Mapping Load
'90511'&'113' as Key
'14/12/2014' as Value
Autogenerate(1)
Map_Table:
Mapping Load
'90511'&'113' as Key
'14/12/2014' as Value
Autogenerate(1)
Hi Sir
This work fine.
Thank
Paul
Hi Saran
This happen only 1 in million. When this happen all my YearMonth field get very messy. Now just using one line load script , it work fine.
Paul