More elegant way of coding this calculation

[Rich S]

Greets, all:

 

I know there's a more elegant way of coding this part of a Let calc but I'm having a brain fade:

Let ( [

Country = CountryAbbreviated1__lxt ;

State1 = StateAbbreviated1__lxt

] ;

Case (
    Country = "CA" and State1 = "NU";
    "Territory";
    Country = "CA" and State1 = "NT";
    "Territory";
    Country = "CA" and State1 = "YT";
    "Territory"
         )

)

 

What is it? The Evaluate command?

Thanks, and cheers,

Rich

 

 

Edited April 9, 2017 by WF7A

[comment]

You could write simply:

If ( CountryAbbreviated1__lxt = "CA" and ( StateAbbreviated1__lxt = "NU" or StateAbbreviated1__lxt = "NT" or StateAbbreviated1__lxt = "YT" ) ; "Territory" )

Or, if you prefer a shorter version:

If ( CountryAbbreviated1__lxt = "CA" and not IsEmpty ( FilterValues ( StateAbbreviated1__lxt ; "NU¶NT¶YT" ) ) ; "Territory" )

 

I see no good reason to use the Let() function only to rename your fields. I also don't see a good reason to use such gobbledygook field names to begin with. And the numeric suffixes raise a red flag: if you're using numbered fields, you almost certainly have a structural issue.

 

Edited April 9, 2017 by comment

[Rich S]

Thanks! Yes, if there's one thing I excel at it's making things vastly more complicated than they should be. (Hey, everybody has a skill.)

I should rename those fields--although they're not number fields (they're text fields), they're currently two sets of state and country fields in the same table and layout: "1" is for a company's HQ  and "2" is for its remote site; better names, for example, would be CountryAbbreviated_HQ and CountryAbbreviated_Remote.

Edited April 10, 2017 by WF7A

[Wim Decorte]

The same principle holds; a company can have a variable number of remotes.  So the locations could be it's own table and the 'hq' or 'remote' becomes just an attribute of the.location.

[Rich S]

That would be true except that for each record (and therefore, company name) there there would only be one HQ and only one remote site (if any), irrespective of how many remote sites there might actually be; if there is a remote site, the user can only enter one. (90% of the time there wouldn't be one.)

Edited April 10, 2017 by WF7A