Forum Discussion
Unable to filter a stylecode
I have a data model with a Y2_Sales table containing sales data, another Y2_Style table with all item information, and a final Y2_Style_color table containing item information at color level.
Table Y2_Style_color contains, for example :
| stylecode | color | retail_pack_color |
|------------|----------------|-------------------|
| CTESV9-RC | 70-BLUE | HISUM |
| CTESV9-RC | 78-JADE | |
| CTESV9-RC | 90-BLACK | |
| CTESV9-RC | 150-OCEAN | |
| CTESV9-RC | 577-DUSTY ROSE | HISUM |
Each reference has several lines according to its colors.
In Y2_Style :
| stylecode | brandname | groupe | sub_group | description | season | sub_class | class |
|-----------|-----------|--------|--------------|------------------|----------|------------------|--------------|
| CTESV9-RC | NIXO | MEN | MEN APPAREL | BOARDWALK JACKSON | ETE 2023 | FITTED WAIST | WALK SHORT |
Each reference has only one line.
Relationships between tables are on stylecode is like this:
I have a table with information from table Y2_Sales and I'd like to have a filter only for colors with retail_pack_color at HISUM so 70-BLUE and 577-DUSTY ROSE. But when I filter, all the colors appear:
What is the reason for this?
Thanks for your help,
4 Replies
If you look at the arrows on your joins you can see which table is filtering another. In your case, Y2_Style filters the Y2_Style_color table and the Y2_Sales. You would need to have Y2_Style_color filtering Y2_Style in order for it to also filter the Y2_Sales table. Perhaps try creating one dimensio table that includes everything from Y2 Style AND color from Y2_Style_color. Then, in that table, concatenate style and color with an underscore between the two field. Then, in your fact table, do the same. This will be your key - join on that.
I've tried putting the filter in both directions between the Y2_Style and Y2_Style_color tables, but it doesn't filter. I've tried linking the Y2_Style_color and Y2_Sales tables via the stylecode directly with a many-to-many relationship in both directions, but it doesn't filter either.
Do you have any other ideas?
Thanks audreygerredDid you try the suggestion of concatenation of style code and color to create the key?
I don't see how I can do this, as the stylecode in Y2_Style is unique and the data is emptied every night and extracted from another program. Each stylecode has several colors, so I'd have to make 1 line with each color, but the stylecode would no longer be unique...
