Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello i am new to qlik sense,
I wanted to make an if in the load script with this form:
if ( color= 'blue' or color= 'yellow' or color= 'pink' or if(color='bright light red ' or color='light red matte','red'), color)
the idea is to have a global field that is called color, but inside color there are two variations of red, why those specifically should be called red and be in the color group.
I tried to include in the if like = '*light red*' 'red' and a wildmatch (color,'*light red*') but it does not group me in color.
COLOR ORIGINAL | COLOR FINAL |
blue | blue |
yellow | yellow |
pink | pink |
bright light red | RED |
light red matte |
can you help me so that then only the color field appears in the table:
In the end I made a mix:
if ( ORIGINAL like '*red*light*', 'RED', ORIGINAL) as COLOR,
if ( WildMatch(COLOR,'*red*light*'), 'RED') or
colour= 'blue' or colour= 'yellow' or colour= 'pink' ) as end_colour
in the pivot table I include colour and break down by final colour
Can you try this:
if (WildMatch([COLOR ORIGINAL],'*red*'), 'RED',[COLOR ORIGINAL]) as Global_Color
test:
load *,
if (WildMatch([COLOR ORIGINAL],'*red*'), 'RED',[COLOR ORIGINAL]) as Global_Color
inline [
COLOR ORIGINAL, COLOR FINAL
blue, blue
yellow, yellow
pink, pink
bright light red, RED
light red matte, RED
];
exit Script;
Thanks for your quick response, but it doesn't work.
Is there a way to do it all in the if? because I would like you to take the two light red words, because I will have more variations in the future and these two are always fixed
Maybe this will help
LOAD *,
if(wildmatch(color, '*light red*', 'red'), color) as Color_Final
RESIDENT YourDataSource;
DROP TABLE YourDataSource;
RENAME TABLE YourDataSource_temp TO YourDataSource;
Yes, you can use multiple nested ifs and wildmatches to do it all in the if.
Another clean may to do it is to define a mapping table. That way, using a simple lookup, you can get the color you want. You can keep updating the table with new combinations. Here's an example.
// Load mapping table of colors
color_map:
mapping LOAD *
Inline [
ORIGINAL,FINAL
blue, blue
yellow, yellow
pink, pink
bright light red, RED
light red matte, RED
] ;
test:
load *,
ApplyMap('color_map',[COLOR ORIGINAL],[COLOR ORIGINAL]) as newcol,
if (WildMatch([COLOR ORIGINAL],'*red*'), 'RED',[COLOR ORIGINAL]) as Global_Color
inline [
COLOR ORIGINAL, COLOR FINAL
blue, blue
yellow, yellow
pink, pink
bright light red, RED
light red matte, RED
];
exit Script;
I am trying to do the if (WildMatch([ORIGINAL COLOR],'*red*'), 'RED',[ORIGINAL COLOR]) as Global_Color and it does not work, it does not detect the LOAD * mapping.
Inline.
is there any way to keep this if ( color= 'blue' or color= 'yellow' or color= 'pink' or if(color='bright light red ' or color='light red matte','red'), color) and include the WildMatch([COLOR ORIGINAL],'*light red*') ?
In the end I made a mix:
if ( ORIGINAL like '*red*light*', 'RED', ORIGINAL) as COLOR,
if ( WildMatch(COLOR,'*red*light*'), 'RED') or
colour= 'blue' or colour= 'yellow' or colour= 'pink' ) as end_colour
in the pivot table I include colour and break down by final colour