Find matching column name with column value

[Dewiatqlik]

Im breaking my head over this and can't find a solution although it looks simple (I thought).

I have two tables one table that contains the columns and headers and the other contains the descriptions of the codes which you can find under the corresponding column.

I think an example would be more in its place here.

[Data Table]:
LOAD * Inline [
HH_AGE, HH_CHILD
0,0
1,2
2,1
3,9
];

[Discription Table]:
LOAD * Inline [
FIELDNAME,CODE,DISCRIPTION
HH_AGE,0,YOUNG
HH_AGE,1,SEMI-YOUNG
HH_AGE,2,TEEN
HH_AGE,3,GROWN-TEEN
HH_CHILD,0,FEW
HH_CHILD,1,MORE_FEW
HH_CHILD,2,EXPECTED
HH_CHILD,9,A_LOT
];

And what I try to get is a new table where the codes in the data table are replaced by the discription in the discription table

So that would look like this:

LOAD * Inline [
HH_AGE, HH_CHILD
YOUNG,FEW
SEMI-YOUNG,EXPECTED
TEEN,MORE_FEW
GROWN-TEEN,A_LOT
];

I hope this is manageable.

Thank you in advance

[Seyko · Accepted solution]

Hello,

To do that, you can use the mapping tables. Try this following code:

[Data Table]:
LOAD * Inline [
HH_AGE,HH_CHILD
0,0
1,2
2,1
3,9
];


[Discription Table]:
LOAD * Inline [
FIELDNAME,CODE,DISCRIPTION
HH_AGE,0,YOUNG
HH_AGE,1,SEMI-YOUNG
HH_AGE,2,TEEN
HH_AGE,3,GROWN-TEEN
HH_CHILD,0,FEW
HH_CHILD,1,MORE_FEW
HH_CHILD,2,EXPECTED
HH_CHILD,9,A_LOT
];

// MAPPING TABLES
MAP_HH_AGE:
MAPPING 
LOAD
	CODE,
    DISCRIPTION
RESIDENT [Discription Table]
WHERE FIELDNAME = 'HH_AGE'
;

MAP_HH_CHILD:
MAPPING 
LOAD
	CODE,
	DISCRIPTION
RESIDENT [Discription Table]
WHERE FIELDNAME = 'HH_CHILD'
;


NoConcatenate
Result:
LOAD
	Applymap('MAP_HH_AGE',HH_AGE,'')   AS HH_AGE,
	Applymap('MAP_HH_CHILD',HH_CHILD,'') AS HH_CHILD
RESIDENT [Data Table];

DROP TABLES
[Discription Table],
[Data Table];

cordially.

[Dewiatqlik]

Woow really thanks! I've been looking for ages for this!

Now it's time to master the mapping function 😜

Greetz