Forum Discussion

shadowsong42's avatar
shadowsong42
Icon for Resolver I rankResolver I
1 year ago
Solved

Calculating a revised plantID with SWITCH

I have a fact table with (among other things) countryCode, countrySubDivisionCode, and plantID - all text columns. For everything other than US and Canada, I want to retain the original plantID. For ...
Need Help
  • vicky_'s avatar
    vicky_
    1 year ago

    Hi, 

    the issue pops up because of the parts List4260, List4790 in the switch statement. Note - both of them are lists, when the SWITCH statement is trying to compare [countrySubDivisionCode] (which is a single string) against your list table of values. 

    To fix this, you can try make the following change:

     

    Calculated Plant = 
VAR List4260 = { "NB", "NL", "NS", "ON", "PE", "QC" }
VAR List4790 = { "AB", "BC", "MB", "NT", "NU", "SK", "YT" }

RETURN
IF( OrderSlaLineItemDetail[countryCode] IN { "CA","US" },
    SWITCH(TRUE(), 
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4260, "4260",
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4790, "4790",
        OrderSlaLineItemDetail[plantId]
    ),
    OrderSlaLineItemDetail[plantId]
)

     

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
1 year ago

Hi, 

the issue pops up because of the parts List4260, List4790 in the switch statement. Note - both of them are lists, when the SWITCH statement is trying to compare [countrySubDivisionCode] (which is a single string) against your list table of values. 

To fix this, you can try make the following change:

 

Calculated Plant = 
VAR List4260 = { "NB", "NL", "NS", "ON", "PE", "QC" }
VAR List4790 = { "AB", "BC", "MB", "NT", "NU", "SK", "YT" }

RETURN
IF( OrderSlaLineItemDetail[countryCode] IN { "CA","US" },
    SWITCH(TRUE(), 
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4260, "4260",
        OrderSlaLineItemDetail[countrySubDivisionCode] IN List4790, "4790",
        OrderSlaLineItemDetail[plantId]
    ),
    OrderSlaLineItemDetail[plantId]
)