Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
mattphillip
Creator II
Creator II

Importing data labels

I'm hoping someone might be able to lend some advice here. I have a large data set (12.5 million rows) and have a large number of columns also, with each column referring to a different question. Each row contains an individual's responses with each response coded (e.g. 0057D). I also have a table which lists all the fields, a field description, each applicable code and its value label (e.g. Building Type X).

Example

Large dataset:

BTypeGlevelWThickness
0057D15B01
0124X10B13
0031Y12N80

Description table:

FieldDescriptionDataLabel
BTypeBuilding type0057DSteel
BTypeBuilding type0124XWood
BTypeBuilding type0031YBrick
GlevelGround level15100
WThicknessWall thicknessN8010

Can someone suggest a way to link/replace the data values in the large data set with the labels within the description table?

Any help would be greatly appreciated.

Yours,

Matt

6 Replies
avinashelite

try with applymap function :

try this:

Discription_Mapping:

Mapping

LOAD Data,

Lable

from

table

Discription;



Large_Data_Set:

Load

Applymap('Discription_Mapping',BType,null()) as Lable,

*

from

large_data_set table;

avinashelite

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is.

Examples:

// Assume the following mapping table:

map1:

mapping load * inline [

x, y

1, one

2, two

3, three ] ;

ApplyMap ('map1', 2 ) returns ' two'

ApplyMap ('map1', 4 ) returns 4

ApplyMap ('map1', 5, 'xxx') returns 'xxx'

ApplyMap ('map1', 1, 'xxx') returns 'one'

ApplyMap ('map1', 5, null( ) ) returns NULL

ApplyMap ('map1', 3, null( ) ) returns 'three'

mattphillip
Creator II
Creator II
Author

Hi Avinash,

I tried your solution and it didn't work .

Any idea why or any other suggestions?

M

awhitfield
Partner - Champion
Partner - Champion

Not too sure if this is what you want, but check out the example

Andy

avinashelite

can you tell what is the exact OUTPUT you want so that we can visualize the same

MayilVahanan

Hi

Are you looking like this?

DescriptionTable:

LOAD * INLINE [

    Field, Description, Data, Label

    BType, Building type, 0057D, Steel

    BType, Building type, 0124X, Wood

    BType, Building type, 0031Y, Brick

    Glevel, Ground level, 15, 100

    WThickness, Wall thickness, N80, 10

];

Description_Mapping:

Mapping

LOAD Distinct Data,

Label

Resident

DescriptionTable;

LargeDataset:

Load *,

Applymap('Description_Mapping',BType, null()) as BTypeLabel

,ApplyMap('Description_Mapping', Glevel, null()) as GlevelLabel

, ApplyMap('Description_Mapping', WThickness, null()) as WThicknessLabel;

LOAD * INLINE [

    BType, Glevel, WThickness

    0031Y, 12, N80

    0057D, 15, B01

    0124X, 10, B13

];

BType BTypeLabel Glevel GlevelLabel WThickness WThicknessLabel
0031YBrick12N8010
0057DSteel15100B01
0124XWood10B13
Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.