How to create a new field filtering another

[Inspiral]

Hello everybody!

I'm very new so i'm gonna try to explain what have I done until now. This would be my database:

Code: XXXXXX+COLOUR (for instance: 123456BL)

Description: Product1 BLUE

Qty: 10

I want to make a new field in which i just aggregate those similar colours.

I mean, ¿how many RED products did I sell? Or, ¿which is the most selled colour?

I have not a Colour field, so i must create it. Is it possible?

Thanks in advance.

[jaibau1993 · Accepted solution]

Hi!

If I have understood you properly then yes, you can create the color dimension. How? The color is in product code. Are the last two letters from the product code always the color of the product? If so, then in the script you can do something like

Load
   Code,
   Right(Code, 2) as ColorCode,
   Other fields...
from whereever;

Thus you create the "color code" dimension (it'll have values like "BL" in your example). You'll need to translate those color codes to real colors:

Load * Inline [
    ColorCode, Color
    BL, Blue
    RD, Red
    ...
];

Hope it helps! Bests,

Jaime. 

[Gysbert_Wassenaar · Accepted solution]

You can create a color field. It looks like your Code field contains information that can be used to determine the color, i.e. the last two letters of the Code.

Colors:
LOAD * INLINE [
ColorCode, Color
BL, Blue
GR, Green
RD, Red
];

Data:
LOAD
Code,
Right(Code,2) as ColorCode,
... other fields ...
FROM ... source table ... ;

[Inspiral]

Well. I'm gonna try and i will tell you.

Edit: I've tried but i have a problem. I connect the Database with ODBC so i need to use SQL sentence instead. How can i translate the code? If I understood it well i must add in the script the following code 

Load
   Code,
   Right(Code, 2) as ColorCode,

But i need to use SQL. Is it possible?

Edit2: Thanks so much! It worked perfectly!