Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
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.
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?
you can simply load your fact table and then line load after.