Please see attached 2018.3 workbook. Screenshot with the calculation is below. To filter out 'S' transactions, add Posting Flag to filters and filter out 'S'. Hope it helps. If yes, please mark as correct/helpful from the original post on the forums. Thx, Don
ContractData.twbx 34.5 KB
Thank you for your response.
Unfortunately, this approach won't work. In reviewing the results, I discovered that not all activities have a "C" level.
(Side note: the [Contract] column being pulled from the actual database doesn't include the word "null." It's just a blank cell/field. I've been testing the formula you suggested on a small sample uploaded from excel, hence the screenshot above).
The way I figured it out in Excel is via a nested IF and INDEX/MATCH function that truncates the activity in three stages, using the following logic:
1) IF posting flag other than "C", THEN
2) remove all characters after the second dash (ex.: SA16937-001-01 to SA16937-001)
a) compare result (In excel INDEX/MATCH or vlookup) (SA16937-001) to column [Contract]. If there's a match - use new result. If not -
3) remove last character from previous result (ex.: SA16937-001 to SA16937-00)
a) compare result (In excel INDEX/MATCH or vlookup) (SA16937-00) to column [Contract]. If there's a match - use new result. If not -
4) remove all characters after first dash (ex.: SA16937-001 or SA16937-00 to SA16937).
a) compare result (In excel INDEX/MATCH or vlookup) (SA16937) to column [Contract]. If there's a match - use new result. If not -
5) "NO CONTRACT"
I hope this makes sense. Also hope this can be achieved in Tableau. I don't plan on creating graphs. I want to create a dashboard that pulls in data from several tables by Contract "C." I was wondering if there's a way of using a tableau workbook as a source for another workbook (using the same database table), but manipulate the joins? Well, that definitely makes no sense. It's 5:55am here in Dallas, TX and I haven't had coffee yet.
Thanks for your time and help!
Hope we can figure out a way.
Post edited 1/1/19. Tried a few new things and got closer...please see attached and below screenshot. Three new calc's get you the following with the added data. Multi-pass effort with multiple calculations and one hidden/suppressed column to get the following view:
If you want those last remaining postings with no contract number association to simply be displayed as 7-characters then change the calculation [Field-Needed] not [Field Needed] to:
IF ISNULL(([Base Calculation])) THEN ATTR(LEFT([Activity],7))
ELSE [Base Calculation] END
Hope it helps! Thx, Don
ContractData.twbx 43.7 KB
Don - not sure what I'm doing wrong, but I can't open the workbook?
Also, the actual tables I'm working with do not have the word 'null' in the [Contract] filed; it's just an empty "cell."
If your formula is based on nulls, is there a way I can change them to look for empty cells or create a new [Contract] columns that inserts 'null' is there are no values?
Thank you very much for helping me with this!
What version of Tableau are you using? If it's different from 2018.3.2 then I'll need to export it and repost to that versioning, if possible. Also, Tableau will (should) automatically recognize empty cells as Nulls which is how I structured it in the Excel file I used to mimic your data structure/output. So there shouldn't be a need to change the calculation at this point. Thx, Don
See if the attached 10.2 workbook and related calc's works for you?
This calc needs to be on Rows:
CALCULATION NAME: Field Needed
IF [Posting Flag]='C' THEN (LEFT([Contract],7))
ELSEIF [Posting Flag]='P' THEN LEFT([Activity],7) END
The above calc simply grabs the 7 digit contract number from the [Activity] or [Contract] field depending on posting type. But I think you wanted those extra digits if possible, which is where things got tricky and required the following two calc's to get those:
This calc needs to be created, but not placed on Rows/Columns. It just needs to be available for the next calc:
CALCULATION NAME: Base Calculation
IF MIN([Activity])=MIN([Contract]) THEN LOOKUP(MIN([Contract]),0)
This calc needs to be on Rows:
CALCULATION NAME: Field-Needed
IF ISNULL(([Base Calculation])) THEN ATTR(LEFT([Activity],11))
ELSE [Base Calculation] END
Then hide the first [Field Needed] calculation by right-clicking and unselect 'Show Header'.
Let's see how things play out with your data from there....Thx Don
ContractData_v10.2.twbx 74.0 KB
I really appreciate your help with this; we're slowly moving in the right direction.
Here's what I have. I created an [Contract-2] column ("replacing" the original [Contract] column) to convert the empty values to 'null.' The formulas you provided wouldn't work on the original [Contract] column (I'm not sure why some fields/entries/cells have 'nulls' and others are just blank with no info, but Tableau treats them differently).
The portion highlighted in yellow - works. The first five digits of a project "SAXXXXX" is the base of the project, if I can say so. A "C" (contract posting flag) can be assigned at any level. In this case, the "C" was assigned at the level below the base level. So, SA19286-001 is a "C", SA19286-002 is a "C" and so on. The formula works in this instance - it's pulling all the children under the "C" level of SA19286-002.
There are projects that don't have "C" levels. These projects can have different levels, but no "C" posting flag. For example, SA19287-01 (highlighted in red). The base of this project is SA19287, and SA19287-01 is the first child. Since there's no "C", the formula is pulling the value from above. This is somewhat misleading, because there may be accounting transactions tagged with this task - SA19287-01. If I was to filter for SA19286-003, SA19287-01 would be pulled in too.
However, I can work with this. I could probably filter by [Field Needed-2], and the filter out what doesn't belong there (haven't actually tried it yet).
What I tried tho, is to join two tables and add more columns. When I place other dimensions on the "Column" bar in Tableau - things get crazy.
Given how to formulas are working, seems like there are limitations on where I can place certain fields. Is my understanding correct?
Should these fields be placed in a particular order for the calculation to work?
Looks like you've got things going, so you're on the right track. But yes, you may be limited on what you can actually do with the end result as the calc's build on each other in order to get you to the view. At least two are 'Table Calc's' and therefore can limit what you do thereafter. It's as far as I could get with it! Hope it helps, thx, Don