topic Re: How to create a new field filtering another in QlikView

How to create a new field filtering another

Inspiral — Sat, 16 Nov 2024 04:24:32 GMT

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.

Re: How to create a new field filtering another

jaibau1993 — Tue, 19 Feb 2019 11:10:20 GMT

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.

Re: How to create a new field filtering another

Gysbert_Wassenaar — Tue, 19 Feb 2019 11:10:46 GMT

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

Re: How to create a new field filtering another

Inspiral — Tue, 19 Feb 2019 12:49:32 GMT

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!