concatenate two fields with the same name in one if

[azucna122]

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:

[pravinboniface]

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;

[azucna122]

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

[BrenoSater]

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;

[pravinboniface]

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;

[azucna122]

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*') ?

[pallavsi_08]

Hi @azucna122 ,

Can you try below script:

if (ORIGINAL like '*red*', 'RED', ORIGINAL) 

[azucna122 · Accepted solution]

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