Keith that's pretty cool, and I like this Idea (the Visio idea), but I'm fairly certain there is nothing currently in the Desktop product that can do this. (I could be wrong.) If you post this in the idea section I'll vote for it.
Thanks K! I hadn't yet voted for that second one.
2 of 2 people found this helpful
In case it helps anyone else- Here is an Excel file to extract the fields and dependencies. No guarantees (not tested on different versions of Excel, or against different versions of Tableau). Developed on 32-bit Excel 2010, used against files created by Tableau desktop 8.1.
The code is unlocked, so anyone can open with macros disabled and inspect the code before using it.
I already got what I needed for my current project so I don't have any plans to tweak or further develop this, but anyone else is welcome to do so.
This doesn't do the full job but it's a pretty good head start: http://tableaufriction.blogspot.ca/2015/02/more-calculated-field-analysis-fields.html
It's a Ruby script that identifies the calculated fields and the fields they reference, saving the results in CSV files. There's a simple Tableau workbook with some basic analyses of the relationships.
I've been trying to come up with a general layout for auto-diagramming the relationships, but after sitting on the basic parsing code for a while didn't want to let the lack of diagramming hold it back.
If anyone has any thoughts on what good diagrams would look like I'd appreciate hearing them. So far I've been using GraphViz for diagramming but am thinking about seeing what D3.js and Neo4j can bring to the party.
This looks really helpful Keith, thanks a lot. Unfortunately I get an error when I try running it.
Does this macro still run flawlessly for you or have Tableau updates broken it?
I've changed roles (and company) and no longer use Tableau, but depending on the complexity of the problem, I'd be happy to help try to troubleshoot.
If you have a couple of small sample workbooks in Tableau's current format (without data!) you could PM them to me via this forum and I can look at and try to determine what has changed- that would be the easiest/fastest option. Please, please make sure any proprietary data has been removed
If you don't have any files that can be cleared of data and shared, we can go down a more complicated path of you posting back more info about the error:
- The actual text of the error message
- Click debug, and in the code window that pops up look for the highlighted a line of code
- share a screenshot showing which row is highlighted, and
- move your mouse over each word in that line, and if a pop-up shows for any of those words ( a pop-up will show the current value of that word), share a list of which words had a pop-up, and what value was shown in the pop-up.
that might give me an idea of what is going on, but remote troubleshooting is fairly difficult
Thank you for trying to help out. I have found a java tool which does the exact same thing so my objective has been met. I highlighted these issues to you in case you are still developing/enhancing your excel tool. So if you don't really require this tool, don;t invest much time and effort into this. Unfortunately I can't share my workbooks. I tried creating a dummy workbook and recreate the problem (by creating a formula which was very similar to the one that caused in error in your tool) but your macro processes it correctly.
So i'll list out the errors that I'm getting in your macro. I'm getting error 5 in the following:
userauth = MsgBox("This macro will delete everything in columns A:E of the active worksheet- are you sure you want to do this?" & _
vbCrLf & vbCrLf & vbCrLf & _
"No warranty or guarantee, use at your own risk", , vbYesNo, "Warning: Cell overwrite = potential for data loss")
Changing to the following resolves it:
userauth = MsgBox("This macro will delete everything in columns A:E of the active worksheet- are you sure you want to do this? Warning: Cell overwrite = potential for data loss")
When I run the code, it gives error 5 further down the line too. Do you think this will help resolve the problem? I tried debugging by using mouseover for values, but did not understand what is causing the error.
If the parameters on Msgbox are causing problems, it may be an issue with
(a) the operating system; are you running on a Windows PC or something else?
(b) using a non-local version of office; are you using a local install or Office 365?
(c) using a non-English version of Excel where some of the hardcoded parameters are language-specific; is your local install of Office an English language install, or something else?
I'd suggest just commenting out the message boxes, but the first one is actually collecting data from the user- so in addition to commenting out all the lines of the userauth = MsgBox (three lines total) you'd also have to add a line
if userauth=6 then....
Of course by doing so, you are eliminating the warning message and the opportunity to abort and save a copy of whatever is currently in the workbook before it is deleted.
I'll wait for your reply on the 3 questions above, and if I can get a TWB file (from anywhere) I can test on my end to see if the original code needs further modification
I was able to get a TWB from another source, and saw the same errors you described. I am using Win7 x64 Excel 2016 x32 (local install), so I suspect there was a windows patch that changed how the messageboxes work.
Because that initial "do you really want to overwrite any old data" isn't critical to the workbook, I removed it (commented it out). I also changed one other line to remove an error I found with this sample TWB that never happened with my workbooks back when I was an active Tableau user.
I've attached an updated copy here, please give it a try and let me know if it works or not.
It's working perfectly now Keith.
I noticed that this workbook (like the Java tool I mentioned in my previous post) capture formulae of calculated fields in terms of internal field/parameter names (also called IDs?) and not in terms of parameter names specified in the UI.
Is there a way you can replace these IDs with actual names? Even though you'll be able to work on it after months, the excel macro you have shared is also immensely helpful. Thanks a lot!
The sample TWB I acquired to test/fix the code is from a project I'm not familiar with, so I can't use it as a base from which to make any type of changes (I just don't know if the output is meaningful).
If you have the time and inclination, create a new TWB off the Tableau training data or a public data source. Add 3-5 calculated fields (what they are doesn't matter).
Run the macro, then add a column and write in the UI variable name, and what name it is replacing (I'm assuming you are referring to column A. I'm assuming that you are somehow renaming the calculations; is you are inserting those into tables and want to retrieve customized column names, I think that is a whole different animal (because you may not use every CF in your tables, and you may use some CFs more than once)
If you do create a simplified TWB, send it my way and I'd be happy to take a look and see whether the changes you'd like are within my skills & bandwidth
A B C "What I would prefer to see" UAC Count Red if [Calculation_3549399458068578309]= Red then 1 else 0 END if UAC Risk= Red then 1 else 0 END Instead of UAC Count Red, the UI name [in the Calculated Field editor] is "UAC failed"
3 of 3 people found this helpful
I have built a shiny app to parse the .twb file and visualise the dependency between calculated fields with the exportable list of variables (performance depend on traffic): https://tony-yanjun.shinyapps.io/Tableau_zen_parser/
The code is here: https://github.com/33Vito/Tableau_Zen_Parser
Feedbacks are most welcome!
This is super helpful Tony, so thank you for this!
One note - for the Calculated Field Table, the export to Excel or CSV only exports one page of fields at a time. So 60 fields show up on 6 different pages by default. I think it would be useful to have a checkbox to 'Show All' records on one page or something to get a 'data dump' when you export.