How to recode using mapping

paulyeo11 — Sat, 22 Feb 2020 13:20:18 GMT

Hi All,

(A) Below is GL Tabe for TDS :-

SOURCE GL_CODE_
TDS 80020

(B) Below is GL Tabe for PMC :-

SOURCE GL_CODE_
PMC 5000200

SOURCE GL_CODE_ GL_CODE_NEW
PMC 5000200 REVENUE
TDS 80020 REVENUE

(D) i Have try below load script :-

MAP_TDS:
LOAD GL_CODE_,
GL_CODE_NEW
FROM
[C:\Users\pauly\Dropbox\5 QV_Final\QV_INPUT\Map_PNL.xlsx]
(ooxml, embedded labels, table is TDS);

MAP_PMC:
LOAD GL_CODE_,
GL_CODE_NEW
FROM
C:\TEMP\Map_PMC.xlsx
(ooxml, embedded labels, table is TDS);

GL_TABLE:
LOAD
'TDS' as SOURCE,
@1:16T as GL_CODE_,
@17:47T as GL_DESC_,
if(@102:103T = '-',@86:101T*-1,@86:101T)*-1 as [Amount],
ApplyMap('MAP_TDS',@1:16T,'OTHERS') as [REV]
FROM C:\TEMP\FS_TDS.TXT (ansi, fix, no labels, header is 0, record is line);

Concatenate
LOAD
'PMC' as SOURCE,
[GL Code],
[GL Code] as GL_CODE_,
[GL Description] as GL_DESC_,
[C/D] as Amount,
ApplyMap('MAP_PMC',[GL Code],'OTHERS') as [REV]
FROM [C:\TEMP\GL_PMC.csv](txt, codepage is 1252, embedded labels, delimiter is ',', msq);

It does not work, hope some one can advise me where i go wrong ?

Remark : The reasons I want to using Mapping is because I can reduce the expression at load script , also my actually application I have about 100 GL Code , any change I can modify at excel file.

Paul

Re: How to recode using mapping

paulyeo11 — Sat, 22 Feb 2020 11:16:44 GMT

Enclosed my ZIP file consists of QVW and Raw data.

topic Re: How to recode using mapping in QlikView

How to recode using mapping

Re: How to recode using mapping