topic Re: Changing field values in QlikView

Changing field values

justcotto — Mon, 11 Mar 2019 13:52:48 GMT

I have a field, (Field1), that contains values 1 through 12. I would like to load this field and apply labels for each numerical value. For example:

1 AS '1- Label1'

2 AS '2- Label2'

....

Thanks

Re: Changing field values

bharathadde — Mon, 11 Mar 2019 14:54:37 GMT

for suppose below is actual table

Facttable:
Column,Field1
A,1
B,2
C,3
D,4
E,5
F,6
G,7
H,8
I,9
J,10
K,11
L,12
from database;

//Create a inline table linking to the table and pull "label" field as dimension

Label:
Load * inline [
Field1,Label
1,1- Label1
2,2- Label2
3,3- Label3
4,4- Label4
5,5- Label5
6,6- Label6
7,7- Label7
8,8- Label8
9,9- Label9
10,10- Label10
11,11- Label11
12,12- Label12
];

Re: Changing field values

jwaligora — Mon, 11 Mar 2019 15:33:06 GMT

Hi,

Another option may be to use a QV Mapping table.

RawData:
Load
	Floor(rand() * 14) as Field1,
	RecNo() as ID
Autogenerate 100
;
	
LabelsMap:
Mapping Load * Inline	
	[Value,Label
	1,1 - Label 1
	2,2 - Label 2
	3,3 - Label 3
	4,4 - Label 4
	5,5 - Label 5
	6,6 - Label 6
	7,7 - Label 7
	8,8 - Label 8
	9,9 - Labeln9
	10,10 - Label 10
	11,11 - Label 11
	12,12 - Label 12];

Load 
	ID,
	Field1,
	ApplyMap('LabelsMap',Field1,'XX - No Label') as Label1
Resident RawData;

Drop Table RawData;

It's a bit more convoluted, but it has 2 benefits I can think of:

1) Reusability - In case you want to apply the same processing to fields with different column names.

2) Explicit labeling of unmapped values. E.g. in the autogenerate I threw in a 0 and a 13, which are "invalid" values in the source data. The ApplyMap function flags them as such, rather than leaving blanks.

The outcome is best observed with a Table Box with the fields: ID, Field1, Label1 displayed.

Re: Changing field values

justcotto — Mon, 11 Mar 2019 15:33:29 GMT

When you say "Column" in the fact table, are you using this as a placeholder? Or can I simply load my fact table and then load inline after?

Re: Changing field values

bharathadde — Mon, 11 Mar 2019 16:55:52 GMT

you can simply load your fact table and then line load after.