9 Replies Latest reply on Jan 4, 2019 9:48 AM by Don Wise

    Parent/Child Calculated Field (Index/ match/ lookup)

    Andrei Tofan

      Hello Tableau Forum Community,

       

      I hope someone can help me with a project I'm working on. Below is a screenshot of data I have in a (single) table (from a database).

      How can I add a field that would keep removing last character (one by one or all characters to the right of second dash, then first dash) until the activity matches the one with a "C" in the "posting flag" column? I'd like to view data by Contract ("C") and see all posting ("P") tasks (ignoring the Summary "S" levels).

      In Excel I was able to create a formula (that includes IF, INDEX/MATCH, LEN, etc.) that does that. I cannot seem to find a workaround in Tableau to get the info I need.

       

      Thank you very much!

        • 1. Re: Parent/Child Calculated Field (Index/ match/ lookup)
          Don Wise

          Hello Andrei

          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

          Screen Shot 2018-12-29 at 12.07.33 PM.png

          • 2. Re: Parent/Child Calculated Field (Index/ match/ lookup)
            Andrei Tofan

            Hello Don,


            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.

            I'm attaching a screenshot:2018-12-29_18-58-51.png

             

            (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.

            • 3. Re: Parent/Child Calculated Field (Index/ match/ lookup)
              Don Wise

              Hi Andrei,

              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:

              Screen Shot 2019-01-01 at 10.48.27 AM.png

               

              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

              • 4. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                Andrei Tofan

                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!

                2019-01-02_11-56-27.png

                • 5. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                  Don Wise

                  HI Andrei,

                  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

                  • 6. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                    Andrei Tofan

                    Don,

                     

                    I'm using version 10.2.0 (64-bit) and I'm also attaching a screenshot with the actual data I'm working with:

                     

                    2019-01-02_12-29-26.png

                     

                    Thank you!

                    • 7. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                      Don Wise

                      Hi Andrei,

                      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)

                      ELSE IFNULL(LOOKUP(MIN([Contract]),0),PREVIOUS_VALUE(MIN([Contract])))

                      END

                       

                      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

                      • 8. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                        Andrei Tofan

                        Hello Don,

                         

                        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?

                         

                        Thank you!

                         

                        2019-01-04_10-46-53.png

                        • 9. Re: Parent/Child Calculated Field (Index/ match/ lookup)
                          Don Wise

                          Hi Andrei,

                          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