TabPy: Python returning Json into Tableau, “Error Parsing Number”

Question

I am trying to use Tableau calculated field to use my python script, which gets JSON data. My ultimate goal is to get this data into tableau in a tabular format.

I have read JSON is easier to get into tableau as opposed to dataframe object.

I currently use it in Spyder program. and execute this to get my data.

print (get1D ("2019-02-02", "2019-02-05", "Tableau", "Limits"))

In my calculated field I get the error: "Error Parsing Number" on

.format(status_code)) 

error msg:

Any help would be appreciated on getting this data into tableau. Here is my full script.

SCRIPT_INT(  

import time  

import requests  

import json  

import pandas as pd  

import re  

import urllib3  

import math  

from io import StringIO  

from datetime import datetime, date,timedelta  

from pandas.tseries.offsets import BDay  

from urllib.parse import urlencode  

from flask import json  

def call_api(url, request_dict, post):     

    if post:  

        header = {'content-type':'application/json'}  

        resp = requests.post(url, data=json.dumps(request_dict), auth=('user', 'pass'), headers = header, verify=False)  

    else:  

        url = url + urlencode(request_dict)  

        resp = requests.get(url, auth=('user', 'pass'), verify=False)  

    status_code = resp.status_code  

    if status_code == 401:  

        raise ValueError("There is an error with the connection.\nLogin failed. \nNot authorized. Please check your credentials and try again.\nStatus code {}".format(status_code))  

    elif status_code == 404:  

        raise ValueError("There is an error with the connection.\nCould not connect to the server.\nStatus code {}".format(status_code))  

    elif status_code == 200:  

        pass  

    else:  

        raise ValueError("There is an error with the connection.\nStatus code {}".format(status_code))  

    return resp  

def getData (startDate, endDate, nodeName, Type, Id):  

    request_dict = [{  

        "hierarchy": "Tableau",  

        "nodeName": nodeName,  

        "FilterId": Type,  

        "Id": Id ,  

    }]  

    url = "https://sampleurl/startDate={0}&endDate={1}"   

    startDate = datetime.strptime(startDate, '%Y-%m-%d')  

    startDate = startDate.strftime ('%Y%m%d')  

    endDate = datetime.strptime(endDate, '%Y-%m-%d')  

    endDate = endDate.strftime ('%Y%m%d')  

    url = url.format(startDate, endDate)  

    resp = call_api(url, request_dict, True)   

    return resp.json ()  

def get1D(startDate, endDate, nodeName, Type):  

    return getData (startDate, endDate, nodeName, Type, 1)  

)   

 

Answer 1

• Refer to theTableau's guide for  Authoring Python Calculations in Tableau.

• Generally, the format needs to be in this way:

SCRIPT_INT("import xyz foo=_arg1+_arg2 return foo", SUM([A number]), SUM([Another Number])

• As per your question is concerned, you'll need to add quotation marks to your calculated field, replace any field variables that need to be passed with _argX, add commas to the calculated field, then a list of fields to pass as those args.

• Take note that anytime you see the error, "The calculation contains errors," in the Tableau Calculated field window that the problem is within the Tableau Calculated field (Formatting/compiling) and not necessarily with the underlying Python. (The error you're seeing is a red herring. The Tableau Calculated Field interpreter is seeing the "." as a decimal and expecting to receive a number behind it.) In the calculated field window, Tableau will have no way of checking the underlying Python - it only passes it to TabPy as a string. The opposite is true as well - seeing "This calculation is valid" in the Tableau Calculated Field window doesn't necessarily mean that the Python script will return properly.

• Below is an example using the code you provided in your question.

  • Double quotations are replaced within Python script with single quotations. In this way, Tableau will be able to tell its own double quotations apart. Tableau is the same as Python in which it treats double and single quotes the same.

  • Replaced the input parameters for getData() with the _arg1 through _arg4.

  • Passed [Start Date], [End Date], [Node Name], and [Type] as args after the Python script is passed as a string. (These get implanted into the string as _arg1 through _arg4. (ATTR() might not be the right aggregation method here - you'll have to experiment.)

  • The Calculated field will now at least compile, however, I cannot guarantee that it will execute on the Python side or that it will accomplish what you're trying to do.

  • I'm not sure how get1D() is going to react here. You may have to put your _arg1 through _arg4 as parameters there too. There is some work that will need to be done - and maybe even a reformat of the code to accept Tableau args.

• Please do read the TabPy documentation for more clarification on its use than I'm able to provide here. Also, here is a good blog post. When used properly, its immensely powerful.

SCRIPT_INT(  

"import time  

import requests  

import json  

import pandas as pd  

import re  

import urllib3  

import math  

from io import StringIO  

from datetime import datetime, date,timedelta  

from pandas.tseries.offsets import BDay  

from urllib.parse import urlencode  

from flask import json  

def call_api(url, request_dict, post):     

    if post:  

        header = {'content-type':'application/json'}  

        resp = requests.post(url, data=json.dumps(request_dict), auth=('user', 'pass'), headers = header, verify=False)  

    else:  

        url = url + urlencode(request_dict)  

        resp = requests.get(url, auth=('user', 'pass'), verify=False)  

    status_code = resp.status_code  

    if status_code == 401:  

        raise ValueError('There is an error with the connection.\nLogin 

        failed. \nNot authorized. Please check your credentials and try 

    again.\nStatus code {}'.format(status_code))  

    elif status_code == 404:  

        raise ValueError('There is an error with the connection.\nCould not 

        connect to the server.\nStatus code {}'.format(status_code))  

    elif status_code == 200:  

        pass  

    else:  

        raise ValueError('There is an error with the connection.\nStatus 

    code {}'.format(status_code))  

    return resp  

def getData (startDate, endDate, nodeName, Type, Id):  

    request_dict = [{  

        'hierarchy': 'Tableau',  

        'nodeName': nodeName,  

        'FilterId': Type,  

        'Id': Id ,  

    }]  

    url = 'https://sampleurl/startDate={0}&endDate={1}'  

    startDate = datetime.strptime(startDate, '%Y-%m-%d')  

    startDate = startDate.strftime ('%Y%m%d')  

    endDate = datetime.strptime(endDate, '%Y-%m-%d')  

    endDate = endDate.strftime ('%Y%m%d')  

    url = url.format(startDate, endDate)  

    resp = call_api(url, request_dict, True)   

    return resp.json ()  

def get1D(startDate, endDate, nodeName, Type):  

    return getData (_arg1, _arg2, _arg3, _arg4, 1)"

,

ATTR([Start Date]),ATTR([End Date]),ATTR([Node Name], ATTR([Type]

)  

Make the right move by enrolling now in Tableau Online Course to learn it.