[SQL] Excel COUNTIFS - count columns matching criteria in a row

Excel in his arsenal of useful functions has COUNTIFS, basically a count of how many elements in a one dimensional range match a specific criteria. It says multidimensional, but it's not, it's either the same criteria twice for more dimensions or a different criteria. Key point: one list at a time.

However this is a very basic need which is not immediately achievable in SQL as well since we cannot loop over columns in a row. That is, unless we remember that PIVOTing is actually a thing. In this specific case we use the inverse operation, UNPIVOT.

[VBA] Excel open popup from macro

As usual when dealing with business users, you'll find yourself tinkering with Excel files which could include macros.

As usual when business users try their hands at this newfangled tech stuff, they will end up putting in a minuscule yet showstopping mistake you need to spot.

Although the VBA debugger is actually pretty good, sometimes the error is just stemming from a wrong value being inserted in a cell. In this case, it's just faster to simply display the value(s) in a popup window and then searhing for the offending cell in the spreadsheet.

The MsgBox function alongside with the CStr one will become your new best friends here:

MsgBox ("I am putting this value " & CStr(your_value) & " but maybe it is wrong")

Where & is used to concatenate strings and your_value can be a variable or an expression within the macro you're debugging.

[TIBCO Spotfire] Export data table as CSV via IronPython

Here's a sample script to programmatically via IronPython export a data table as CSV

from Spotfire.Dxp.Data.Export import DataWriterTypeIdentifiers
from System.IO import File

writer = Document.Data.CreateDataWriter(DataWriterTypeIdentifiers.ExcelXlsDataWriter)
table = Document.ActiveDataTableReference #OR pass the DataTable as parameter
filtered = Document.ActiveFilteringSelectionReference.GetSelection(table).AsIndexSet() #OR pass the filter
stream = File.OpenWrite("PATH/NAME.csv")
names = []
for col in table.Columns:
    names.append(col.Name)
writer.Write(stream, table, filtered, names)
stream.Close()




Note: the path you pass to the File.OpenWrite function is relative to the machine running the analysis; this means that on the Web Player, unless you find a way to stream the data back to the user browser, the end user will never receive the file

[TIBCO Spotfire] Excel JDBC data source template

If you want to take full advantage of the Information Designer capabilities when connecting to non-default data sources, and your analysis can't be fully developed using only Connectors and/or ADS, you might want to create your own data source template. [1]

The idea is simple: as long as you have a proper JDBC driver and can supply basic details on how to connect and handle operations, Spotfire Server allows you to add a custom connection template that end users will be able to select when creating a new Data Source. [2]

Here is a sample connection template for Excel. Note that in this case you will need the ODBC driver as well.

 <jdbc-type-settings>   
  <!-- Informative name and display-name -->   
  <type-name>ODBC-EXCEL</type-name>   
  <!-- point out the JDBC-ODBC bridge -->   
  <driver>sun.jdbc.odbc.JdbcOdbcDriver</driver>   
  <!-- Pattern displayed to administrator when setting up the Datasource -->   
  <connection-url-pattern>jdbc:odbc:excel-odbc-source</connection-url-pattern>   
  <!-- Table types allowed for EXCEL is TABLE and SYSTEM TABLE -->   
  <table-types>TABLE, SYSTEM TABLE</table-types>   
  <!-- As ping command we will use the integer constant 1. Could really be any pseudo function. -->   
  <ping-command>SELECT 1</ping-command>   
  <connection-properties />   
  <!-- Excel does not support catalogs nor schemas -->   
  <supports-catalogs>false</supports-catalogs>   
  <supports-schemas>false</supports-schemas>   
  <!-- Found an error in excel not allowing Distinct in combination with order-by on all columns. -->   
  <!-- Choice between supporting order by and distinct in favor for distinct -->   
  <!-- to make prompt without duplicates and support distinct conditioning. -->   
  <supports-order-by>false</supports-order-by>   
  <!-- Format pattern for date, time and datetime (same as timestamp). -->   
  <date-literal-format-expression>{d '$$value$$'}</date-literal-format-expression>   
  <time-literal-format-expression>{t '$$value$$'}</time-literal-format-expression>   
  <date-time-literal-format-expression>{ts '$$value$$'}</date-time-literal-format-expression>   
  </jdbc-type-settings>   

[1] Data source template documentation - Chapter 11.5
[2] Sample guidelines to add Attivio as data source

Excel spreadsheet functions - Funzioni foglio di calcolo

When working with Microsoft Excel, you will find the need to use spreadsheet functions sooner or later. Those functions can be put directly into a cell and do not require any knowledge of Basic.

However, their names are translated into the application language so non English copies will have localized function names; this means you will need to translate them into your locale before being able to use them otherwise you will get the error #NAME?

Remember that this operation is not necessary in order to view Excel files as upon opening they will automatically be converted to work with the application locale.

Here is a descriptive list of Excel 2003 functions.

Here you can translate function names between different languages.

Note: the same applies to OpenOffice.org / LibreOffice Calc