Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Dewiatqlik
Contributor
Contributor

Find matching column name with column value

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

Labels (1)
1 Solution

Accepted Solutions
Seyko
Partner - Creator
Partner - Creator

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.

 

Excuse my english, i'm french!

View solution in original post

2 Replies
Seyko
Partner - Creator
Partner - Creator

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.

 

Excuse my english, i'm french!
Dewiatqlik
Contributor
Contributor
Author

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

 

Now it's time to master the mapping function 😜

 

Greetz