Help

Re: SKU Creation Formula

Solved
Jump to Solution
2522 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Kevin_Harrison
4 - Data Explorer
4 - Data Explorer

Hey all,

I’m attempting to create a formula that will automatically create SKUs from several different columns, but I’m running into a few issues.

Below is the formula I have so far:

SWITCH(

{Brand Code},

"Kala","KA",

"UBASS","UB",

"Makala","MK",

"Ukadelic","UK",

"Kala Percussion","KP",

"Kala Elite USA","ELT"

)&"-"&

SWITCH(

{Product Type},

"Acoustic-Electric","AE-",

"Guitar","GTR-"

)

&SWITCH(

{Type Code},

"Solid Top (2)","2",

"All Solid (3)","3",

"Solid Body (6)","6"

)

&SWITCH(

{Top Wood Code},

"Cedar","C",

"Spruce","S",

"Striped Ebony","SE",

"Koa","K",

"Acacia","A",

"Mahogany","M"

)

&SWITCH(

{Back & Sides},

"Mahogany","M",

"Rosewood","R"

)

&SWITCH(

{Size Code},

"Tenor","-T"

)Preformatted text

I want the {Back & Sides} portion of the formula to only show up if it doesn’t match with the {Top Wood Code}. For example in row 3 I have a ukulele that has its top, back, and side wood listed as Mahogany, but my current formula makes the SKU “MK-MM”. I want it to be “MK-M”. My formula knowledge is pretty basic so any help will be greatly appreciated.

Reference photo:
image

1 Solution

Accepted Solutions

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)

...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....

See Solution in Thread

4 Replies 4

Hi @Kevin_Harrison, I think we could just use an IF() to check whether the the top matches the back and sides and output the one switch instead of two

I’ve put something together below that should work I think, but as I can’t test it I can’t guarantee anything

SWITCH(
  {Brand Code},
  "Kala","KA",
  "UBASS","UB",
  "Makala","MK",
  "Ukadelic","UK",
  "Kala Percussion","KP",
  "Kala Elite USA","ELT"
) &
"-" &
SWITCH(
  {Product Type},
  "Acoustic-Electric","AE-",
  "Guitar","GTR-"
) &
SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
IF(
  {Top Wood Code} = {Back & Sides},
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ),
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
  SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
)

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)

...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....

Oooh, yes, you’re right, your way is a lot cleaner and wouldn’t require changes to be made in two places

Kevin_Harrison
4 - Data Explorer
4 - Data Explorer

Thanks for the assistance on this guys, I actually got pretty close to this yesterday, I was just missing the “!”. The rest of the formula should be a breeze!