Case Sensitivity (capitalization)

Comments

7 comments

  • Avatar
    Malinda Jepsen

    This doesn't impact us because 90% of our data comes from SQL Server and we use INT or BIGINTs for our keys since they are much more efficient.

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Michael,

    I think it is highly dependent on case. What if you would actually want a join which is Case Sensitive in some cases? Blanket solutions that force one approach or another in my opinion cause more issues down the road. If you have possibility to decide individually for each column, than you have better control over what is happening. I think that this kind of situations show rather problems with data, than with tool that works with it. Having text fields for joins which have upper and lower cases and supposed to be joined on ignoring case, that sounds to me like bad database design.

    Regards,

    Michał

    0
    Comment actions Permalink
  • Avatar
    Michael Wozniak

    I do not have control over the database I am using for reporting.  So, the data is not ideal.  I acknowledged above that there might be some edge cases where you would want case-sensitive joins, but I am asking here whether any Sisense users actually need that functionality.  The default for the major types of SQL I have encountered is to ignore case.

    Does anyone actually currently need case-sensitive matching for any reason?

    0
    Comment actions Permalink
  • Avatar
    Simon Tuson

    I stumbled across this while researching an issue I have with case sensitivity and inconsistency in my external data sources. External in the sense that I don't own them.

    In my case I am having to create additional "Normalized" columns to transform and clean this data. Not ideal but can be workable.

    This is obviously essential to provide clean and consistent Dimension Tables that are commonly used as filters.

    Sisense offer the "lower" and "upper" functions but I am struggling with the requirement of leading caps. Anyone else have this problem?

    With regards to joins it would not be that difficult to have an option apply an UPPPER transformation on the fly if requested and thus remove case inconsistencies

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    In general, at least in my experience, 95% of cases rely on int or bigint identifiers, text id's are not desired to cases as well as white spaces(trailling or leading). In general I think option like that could be added, but then it can be risky, since you can have two different strings, which differ only with Case on first letter example, and they could mean two different identifires.

    @Simon - try this one for your case - Upper(left(lower([ShipCity]),1))+RemoveAt(lower([ShipCity]),0) - this should be a new custom column that you should connect through.

    1
    Comment actions Permalink
  • Avatar
    Michael Becker

    In general, at least in my experience, 95% of cases rely on int or bigint identifiers, text id's are not desired to cases as well as white spaces(trailling or leading). In general I think option like that could be added, but then it can be risky, since you can have two different strings, which differ only with Case on first letter example, and they could mean two different identifires.

    @Simon - try this one for your case - Upper(left(lower([ShipCity]),1))+RemoveAt(lower([ShipCity]),0) - this should be a new custom column that you should connect through.

    0
    Comment actions Permalink
  • Avatar
    Lisa Jay

    I am now facing the same issue, I have multiple external data sources where the 'Model' field is sometimes a list of values in one data source but in another it is free text.

    I am struggling to script every potential possibility because of the case sensitive issue.

    I expected the case when statement to ignore case when using the Like %

     

    0
    Comment actions Permalink

Please sign in to leave a comment.