Case 887 User enter wrong date , How to use Mapping to correct it ?

[paulyeo11]

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  ?

[vamsee · Accepted solution]

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 

[vamsee]

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

[Saravanan_Desingh]

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.

[paulyeo11]

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)

[paulyeo11]

Hi Sir

This work fine.

Thank 

Paul

[paulyeo11]

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