Case 008 Recode 2 Diff field by Mapping

[paulyeo11]

(1) My Excel file = GL_MAPPING_TABLE.csv with below info :-

GL_CODE_A … GL_
10 … STOCK

(2) Below is my Load Script :-

MAP_STOCK:
MAPPING LOAD
GL_CODE,
GL_
FROM
GL_MAPPING_TABLE.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

GL_TABLE:
LOAD * INLINE [
GL_CODE_A,GL_DESC,DOC_NO,Amount,date,SOURCE
10,STOCK,1,4,14/4/2019,COY_A
10,STOCK,2,4,14/4/2020,COY_A
20,REVENUE,3,6,1/4/2020,COY_A
10,STOCK,4,69,23/4/2020,COY_A
20,REVENUE,5,9,24/4/2020,COY_A
];

Concatenate

//GL_TABLE:
LOAD * INLINE [
GL_CODE,GL_DESC,DOC_NO,Amount,date,SOURCE
11,STOCK,1,5,14/4/2019,COY_B
11,STOCK,2,6,14/4/2020,COY_B
20,REVENUE,7,6,1/4/2020,COY_B
11,STOCK,4,60,23/4/2020,COY_B
20,REVENUE,5,19,24/4/2020,COY_B
];

TABLE_P:
left keep (GL_TABLE)
load GL_CODE,GL_CODE_A,

ApplyMap('MAP_STOCK',[GL_CODE_A],'NON_STOCK') as [STOCK_M]

resident GL_TABLE;

May i know how to make STOCK_M return value = STOCK and NON_STOCK ?

Hope some one cn advise me.

Paul

[rubenmarin]

Hi, I don't understand the requirements, What's the expected result from that inline data?

[paulyeo11]

Hi Sir

I expected below apply map expression return the Field, But it didn’t.

ApplyMap('MAP_STOCK',[GL_CODE_A],'NON_STOCK') as [STOCK_M]

As I can use the field to filter those row.

Paul

[rubenmarin]

The middle table does a concatenation of a table with [GL_CODE] to another with [GL_CODE_A], so each row will have only one value and applymap only will return value of the table with [GL_CODE_A] field.

Posting the expected result can help to understand what are you trying to do.

[paulyeo11]

Hi Sir

In what way I can make them concatenate?
It is possible by left or right join ?

Paul

[rubenmarin · Accepted solution]

Hi Paul, I still don't know what's the expected result, maybe it's just renaming the field while concatenating to both tables have the same field names

[paulyeo11]

Hi Sir

Thank you very much, I will rename both table into same name.

Paul