Calculated fields

[benlower]

Add support for calculated fields

/cc @DataRoberts

[MikeRobertsPS]

Pretty much the same need as custom sql: updating similar workbooks across sites and also to save some time (and potential error) as one has to download, open, update and republish.

[MikeRobertsPS]

Here's an example: https://github.com/DataRoberts/Tableau/blob/master/TableauDesktop/TsChangeFormula.ps1

[bryanthowell-tableau]

Calculations are just column aliases -- https://tableauandbehold.com/2016/06/29/defining-a-tableau-data-source-programmatically/

"Calculations are defined as column aliases, with an additional calculation tag inside. They are assigned an internal name which follows the pattern “Calculation_” + a 19-digit random number. If you make any Data Source Filters, you have to use this automatically generated name as the reference to this column. The calculation is simply plain text in the formula attribute, however any single or double quote in the calculation definition must be encoded:"

So the ability to create new aliases gives you the calculations pretty simply.

I've implemented the creation of new aliases / column nodes, including calculations with the following code. I haven't implemented editing yet for calculations, although of course it's just the formula attribute within that with the calculation node inside the column node.

    @staticmethod
    def create_random_calculation_name():
        n = 19
        range_start = 10 ** (n - 1)
        range_end = (10 ** n) - 1
        random_digits = random.randint(range_start, range_end)
        return u'Calculation_{}'.format(unicode(random_digits))

    def add_column_alias(self, tableau_field_alias, caption=None, dimension_or_measure=None,
                         discrete_or_continuous=None, datatype=None, calculation=None):
        if dimension_or_measure.lower() in [u'dimension', u'measure']:
            role = dimension_or_measure.lower()
        else:
            raise InvalidOptionException("{} should be either measure or dimension".format(dimension_or_measure))

        if discrete_or_continuous.lower() in [u'discrete', u'continuous']:
            if discrete_or_continuous.lower() == u'discrete':
                if datatype.lower() in [u'string']:
                    t_type = u'nominal'
                else:
                    t_type = u'ordinal'
            elif discrete_or_continuous.lower() == u'continuous':
                t_type = u'quantitative'
        else:
            raise InvalidOptionException("{} should be either discrete or continuous".format(discrete_or_continuous))

        if datatype.lower() not in [u'string', u'integer', u'datetime', u'date', u'real', u'boolean']:
            raise InvalidOptionException("{} is not a valid datatype".format(datatype))

        self.column_aliases[tableau_field_alias] = {u"caption": caption,
                                                    u"type": t_type,
                                                    u"datatype": datatype.lower(),
                                                    u"role": role,
                                                    u"calculation": calculation}

    def add_calculation(self, calculation, calculation_name, dimension_or_measure, discrete_or_continuous, datatype):
        internal_calc_name = self.create_random_calculation_name()
        self.add_column_alias(internal_calc_name, calculation_name, dimension_or_measure, discrete_or_continuous,
                              datatype, calculation)
        # internal_calc_name allows you to create a filter on this
        return internal_calc_name


    def generate_aliases_column_section(self):
        column_aliases_array = []

        # Now to put in each column tag
        for column_alias in self.column_aliases:
            c = etree.Element(u"column")
            # Name is the Tableau Field Alias, always surrounded by brackets SQL Server style
            c.set(u"name", u"[{}]".format(column_alias))
            if self.column_aliases[column_alias][u"datatype"] is not None:
                c.set(u"datatype", self.column_aliases[column_alias][u"datatype"])
            if self.column_aliases[column_alias][u"caption"] is not None:
                c.set(u"caption", self.column_aliases[column_alias][u"caption"])
            if self.column_aliases[column_alias][u"role"] is not None:
                c.set(u"role", self.column_aliases[column_alias][u"role"])
            if self.column_aliases[column_alias][u"type"] is not None:
                c.set(u"type", self.column_aliases[column_alias][u"type"])
            if self.column_aliases[column_alias][u'calculation'] is not None:
                calc = etree.Element(u'calculation')
                calc.set(u'class', u'tableau')
                # quoteattr adds an extra real set of quotes around the string, which needs to be sliced out
                calc.set(u'formula', quoteattr(self.column_aliases[column_alias][u'calculation'])[1:-1])
                c.append(calc)
            column_aliases_array.append(c)
        return column_aliases_array