Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Woow really thanks! I've been looking for ages for this!
Now it's time to master the mapping function 😜
Greetz