Forum Discussion

Kreg's avatar
Kreg
Icon for Helper I rankHelper I
3 years ago
Solved

Query does not work as expected

Hi,   I have a query which works partially: Model Type =  IF (LEFT(model[type], 8) = "https://" ||  OR(     ISNUMBER(model[type])     ,0     ),     "Accepted","Failed")   Now, you can ...
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    Kreg ,

    Here is a slight modification:

     

     

    Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

     

     

    Type                                       Accepted/Failed

    https://tesla.com/345345 Accepted
    345345 Accepted
    - Failed
    Not Applicable Failed

    The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

    Regards,

rsbin's avatar
rsbin
Icon for Community Champion rankCommunity Champion
3 years ago

Kreg ,

Looks like there are a few issues with your syntax.  Please try this:

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8) = "https://", "Accepted",
                      ISNUMBER( VALUE([Type] )) = TRUE(), "Accepted",
                      "Failed" )

 

 

I trust I interpreted your question correctly.

Regards,

Kreg's avatar
Kreg
Icon for Helper I rankHelper I
3 years ago

You interpreted it very correctly but I missed that in some rows might be some string provided by the user like "-", "Not Applicable", etc. and when I used your query I got message:
"Cannot convert value '-' of type Text to type Number."

So lets say that everything those kind of input must be treated as Failed. 

  • rsbin's avatar
    rsbin
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Kreg ,

    Here is a slight modification:

     

     

    Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

     

     

    Type                                       Accepted/Failed

    https://tesla.com/345345 Accepted
    345345 Accepted
    - Failed
    Not Applicable Failed

    The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

    Regards,

    • Kreg's avatar
      Kreg
      Icon for Helper I rankHelper I
      3 years ago

      Thats IN really works and looks very smart. Thanks for that. It works for me.