Editing An Existing SQL Custom Import Query
Question: I want to edit existing Custom Import Query, how can I do so? Solution: From the context menu open on the table with custom import query Change connection settings for table: From there you will be able to see your custom import query and edit it. It is also described here.Removing Leading Zeros
Question: What is the best way to remove leading zeros? I have a field that is alphanumeric values (JONESBJ001, SMITH KC02, 0000012345, 0000555213. Solution: If all of your data with leading zeros will always be some sort of numerical string (ie there will not be values like 000{character}) you could create a custom column using the following expression in an Elasticube: CASE WHEN Left([Values],1) = '0' THEN ToString(ToInt([Values])) ELSE [Values] ENDHow To Concate Three Strings In Elasticube
Question: I have requirement to combine and show the dimension. There are two dimensions Product and Product_Family. I want to display it as single dimension in charts like "Product 1 | Product Family 1", "Product 2 | Product Family 1", so on. Solution: If all dimensions are of string data type it will be enough to use the '+' sign between them and the '|'. If they are not strings yet, you can use the TOSTRING() function before using the '+' You can also use the Concat function: Concat(Concat([Product1].''),[Product2]) Or Concat([Product1],[Product2])Accumulation Build For Millisec (Ms) By Using Unix Timestamp Transformation
Question: This post we will talk about accumulation via millisec by using a unix timestamp transformation In this use case, We will be working with a CData Elasticsearch ODBC driver and client need a way to accumulate data base off milliseconds. Naively, we are unable to accumulate down to the millisec but with this work-around, we are able to do so. Solution: This will be done upon data pull in from your data source where we will edit the query and transform it into a unix timestamp Please keep in mind this is using function from your native data source. In this case, we are using ODBC CData and they support the function we needed for this transformation By using the following function, we are able to bring in data and transform into unixts SELECT datediff('millisecond' ,DATEFROMPARTS(1970, 1,1),datetime) FROM demosisense.testDate After this has been complete, you can now go ahead and set the accumulation base off the new field we created. Following regular Sisense function, we can then hide this field while do an accumulative build on this. And this will accumulate down to the millisec!Using SQL Coalesce To Handle Null Values
Question: SQL NULL is not technically a value, but rather a state. Fields are not equal or not equal to null, they are or are not null. As such, null fields can get a little tricky when attempting different kinds of transformations. For example, mathematical equations across multiple fields where any of the fields are null will result in a null value: 5 * null = null null – null = null 5 + null = null Consider a salary calculation where an employee as Base but no Commission: Solution: In this case the Total for Regina should equal 49000, not null. To transform the null into a not null value like 0 we could use SQL Case: More efficiently we can use Coalesce which checks for null values in the specified expression (i.e., Base or Commission) and does the translation to the specified value (i.e., 0): Coalesce is also useful for finding the first populated (i.e., non-null) value across multiple fields in the same record (e.g., select the first non-null phone number from home, mobile, office fields). Using SQL Case: The same logic using Coalesce results in more efficient and decipherable query: Both queries bear the same correct result:Split A Delimited Field Into Rows
Question: A data source in your ElastiCube contains a field with multiple values in it delimited by a particular character (i.e - comma, semicolon, space, etc.). You need to parse that field and create a table with a record for every value in the delimited field. For our example we will be working with a .csv file ("emails") containing only two columns - RowID and emails. The emails.email field contains multiple emails addresses delimited by a comma: Solution: Import the table containing the delimited field into your ElastiCube as a standalone data source (meaning do not connect it to the larger data model via a relationship). This table can be hidden to prevent dashboard designers unfamiliar with the schema for using it in dashboards, and will act only as the source for our custom table. Solution is data source agnostic - so it doesn't matter whether this source is a SQL database, Excel workbook, SalesForce connector, etc Create a custom table ("+ Custom" at the top of the web ElastiCube manager). For the purposes of this example the custom table will be named "emails_delimited". Paste the following code snippet: SELECT * FROM ( SELECT *, 1 AS n, StrParts([email], ',', 1) AS email_split FROM [emails] UNION ALL SELECT *, 2 AS n, StrParts([email], ',', 2) AS email_split FROM [emails] UNION ALL SELECT *, 3 AS n, StrParts([email], ',', 3) AS email_split FROM [emails] UNION ALL SELECT *, 4 AS n, StrParts([email], ',', 4) AS email_split FROM [emails] UNION ALL SELECT *, 5 AS n, StrParts([email], ',', 5) AS email_split FROM [emails] UNION ALL SELECT *, 6 AS n, StrParts([email], ',', 6) AS email_split FROM [emails] ) emails_delimited WHERE email_split IS NOT NULL AND email_split != '' UNION ALL SELECT *, 1 AS n, [email] AS email_split FROM [emails] WHERE [email] NOT LIKE '%,%' Replace the following columns with fields applicable to your use case [email] = delimited field ',' = your delimiter (don't forget to replace in the last line of code in the snipped as well!) email_split = split field name [emails] = initial source table Preview to confirm the custom table is returning as expected - in our emails example: Note that in Step 2 - you will need a row in the SELECT clause for the maximum possible values parsing the delimited field will yield. For example in the code snippet above, there are 6 SELECT's as parsing RowID 2 yields 6 emails (RowID1 yields 3, RowID3 yields 1, etc.)