topic Re: Case 887 User enter wrong date , How to use Mapping to correct it ? in QlikView

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

paulyeo11 — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

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

vamsee — Tue, 09 Jun 2020 15:05:09 GMT

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

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

vamsee — Tue, 09 Jun 2020 15:20:49 GMT

However, you can also use a mapping table if you have multiple replacements

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

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

Saravanan_Desingh — Tue, 09 Jun 2020 17:18:17 GMT

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.

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

paulyeo11 — Wed, 10 Jun 2020 01:25:31 GMT

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)

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

paulyeo11 — Wed, 10 Jun 2020 01:26:13 GMT

Hi Sir

This work fine.

Thank

Paul

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

paulyeo11 — Wed, 10 Jun 2020 01:28:00 GMT

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