Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Inspiral
New Contributor

How to create a new field filtering another

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.

Labels (2)
2 Solutions

Accepted Solutions
jaibau1993
Contributor II

Re: How to create a new field filtering another

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. 

MVP & Luminary
MVP & Luminary

Re: How to create a new field filtering another

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

talk is cheap, supply exceeds demand
3 Replies
jaibau1993
Contributor II

Re: How to create a new field filtering another

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. 

MVP & Luminary
MVP & Luminary

Re: How to create a new field filtering another

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

talk is cheap, supply exceeds demand
Inspiral
New Contributor

Re: How to create a new field filtering another

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!