topic Re: Find matching column name with column value in Connectivity & Data Prep

Find matching column name with column value

Dewiatqlik — Tue, 28 Dec 2021 19:33:49 GMT

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

Re: Find matching column name with column value

Seyko — Tue, 21 May 2019 13:01:49 GMT

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.

Re: Find matching column name with column value

Dewiatqlik — Tue, 21 May 2019 13:32:59 GMT

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

Now it's time to master the mapping function 😜

Greetz