Forum Discussion
Switch Column Logic Help
Hi Greg_Deckler & AlB
Thanks for the feedback. I have pasted sample data below:
| EE ID | Country | Division | Layer 1 | Layer 2 |
| 5464 | Italy | Cameras | Paul | Beth |
| 5221 | Greece | Cars | Paul | Joanie |
| 5845 | Turkey | Cars | Paul | Dan |
| 231132 | Turkey | Corporate | Paul | Bill |
| 112258 | Greece | Corporate | Paul | Christina |
| 5843 | USA | Cars | Paul | Steven |
| 123 | USA | Imaging | Paul | Jacob |
| 46546 | USA | Photos | Paul | Matt |
| 12132468 | Sweden | Audio | Paul | Peter |
| 546544 | Monaco | Corporate | Paul | Edward |
| 13132 | France | Software | Paul | Joey |
| 12132 | Italy | Audio | Paul | Bobby |
Regarding my original post, I want to create a calculated column called "Label". The layer 2 name should return the result of what I have outlined above in my original post. However, if the country is ITALY, GREECE or TURKEY, then I want it to be called "CARS" regardless of that person's name and desired value I outlined above. I essentially want the country to take over the label from the switch function and that if those 3 countries are NOT mentioned then it gives me the label that I have outlined above -- please note that Steven is also in charge of "Cars" though he is in the USA, which is fine.
As you can see from the original post, Bobby would be audio, but since he has headcount in Italy, I would want Cars to be the label instead since he has headcount in Italy. The final table should look like this:
| Label | Count |
| Cars | 7 |
| Imaging | 1 |
| Audio | 1 |
| Corporate | 1 |
| Software | 1 |
| Photos | 1 |
I also want to use this as a field to put into a bigger data table for records export so that it looks like this:
| EE ID | Country | Division | Layer 1 | Layer 2 | Label |
| 5464 | Italy | Cameras | Paul | Beth | Cars |
| 5221 | Greece | Cars | Paul | Joanie | Cars |
| 5845 | Turkey | Cars | Paul | Dan | Cars |
| 231132 | Turkey | Corporate | Paul | Bill | Cars |
| 112258 | Greece | Corporate | Paul | Christina | Cars |
| 5843 | USA | Cars | Paul | Steven | Cars |
| 123 | USA | Imaging | Paul | Jacob | Imaging |
| 46546 | USA | Photos | Paul | Matt | Photos |
| 12132468 | Sweden | Audio | Paul | Peter | Audio |
| 546544 | Monaco | Corporate | Paul | Edward | Corporate |
| 13132 | France | Software | Paul | Joey | Software |
| 12132 | Italy | Audio | Paul | Bobby | Cars |
I hope this helps! Thanks so much all!!!!
Anonymous - what is the incorrect result that you're getting?
Anonymous
I'm trying to write logic like this to get the above return, but not having luck:
Label = VAR _Country = SWITCH( TRUE (), ActiveHC[Country Name] = "Turkey", "Cars", ActiveHC[Country Name] = "Greece", "Cars", ActiveHC[Country Name] = "Italy", "Cars", Blank() ) VAR _Segment = SWITCH( True(), ActiveHC[Layer 2] = "Beth", "Corporate", ActiveHC[Layer 2] = "Joanie", "Corporate", ActiveHC[Layer 2] = "Dan", "Corporate", ActiveHC[Layer 2] = "Bill", "Corporate", ActiveHC[Layer 2] = "Christina", "Corporate", ActiveHC[Layer 2] = "Steven", "Cars", ActiveHC[Layer 2] = "Bobby", "Audio", ActiveHC[Layer 2] = "Matt", "Photos", ActiveHC[Layer 2] = "Peter", "Photos", ActiveHC[Layer 2] = "Edward", "Photos", ActiveHC[Layer 2] = "Joey", "Software", ActiveHC[Business Unit] = "Cameras", "Cameras", BLANK () ) ) RETURN IF _COUNTRY
Essentially, If Country is equal to one of those 3 countries I want it to be called "CARS" -- I want that to default regardless of the additional criteria I have below. However, if the country does NOT equal those 3, I want it to return the other values I have broken out.
I just can't get the RETURN IF COUNTRY thing right using the 2 VAR pieces.
Anonymous - Does this work?:
Label =
SWITCH(
TRUE(),
ActiveHC[Country Name] IN { "Turkey", "Greece", "Italy" }, "Cars",
ActiveHC[Layer 2] IN { "Beth", "Joanie", "Dan", "Bill", "Christina" }, "Corporate",
ActiveHC[Layer 2] = "Steven", "Cars",
ActiveHC[Layer 2] = "Bobby", "Audio",
ActiveHC[Layer 2] = IN { "Matt", "Peter", "Edward" }, "Photos",
ActiveHC[Layer 2] = "Joey", "Software",
ActiveHC[Business Unit] = "Cameras", "Cameras",
BLANK()
)Thanks for this, but it will not work. That's a standard SWITCH which I can do, I'm trying to make it so the COUNTRY is the default return if it is NOT one of those countries, then return the other switch function.