Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
justcotto
Contributor III
Contributor III

Changing field values

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

4 Replies
bharathadde
Creator II
Creator II

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
];

jwaligora
Creator
Creator

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.

J.

 

justcotto
Contributor III
Contributor III
Author

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?

bharathadde
Creator II
Creator II

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