Changing field values

[justcotto]

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

[bharathadde]

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]

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]

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]

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