Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Dewiatqlik
New 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 (4)
1 Solution

Accepted Solutions
Partner
Partner

Re: Find matching column name with column value

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!
2 Replies
Partner
Partner

Re: Find matching column name with column value

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
New Contributor

Re: Find matching column name with column value

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

 

Now it's time to master the mapping function 😜

 

Greetz