topic Importing data labels in QlikView

Importing data labels

mattphillip — Mon, 10 Aug 2015 11:39:10 GMT

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:

BType	Glevel	WThickness
0057D	15	B01
0124X	10	B13
0031Y	12	N80

Description table:

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

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

Re: Importing data labels

avinashelite — Mon, 10 Aug 2015 12:28:45 GMT

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;

Re: Importing data labels

avinashelite — Mon, 10 Aug 2015 12:30:31 GMT

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'

Re: Importing data labels

mattphillip — Mon, 10 Aug 2015 13:26:29 GMT

Hi Avinash,

I tried your solution and it didn't work .

Any idea why or any other suggestions?

Re: Importing data labels

awhitfield — Mon, 10 Aug 2015 14:25:49 GMT

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

Andy

Re: Importing data labels

avinashelite — Tue, 11 Aug 2015 05:53:50 GMT

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

Re: Importing data labels

MayilVahanan — Tue, 11 Aug 2015 08:23:19 GMT

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
0031Y	Brick	12		N80	10
0057D	Steel	15	100	B01
0124X	Wood	10		B13