10 Replies Latest reply on Jan 31, 2014 12:28 PM by Jonathan Drummey

    DOES NOT =

    Ben Shirley

      Hi all,

       

      If you are comparing fields for matching string or text etc.. how can you say, this does not = that.

       

      So my current code is this:

       

      IF

      ATTR([Series Title])=ATTR([Sheet1 (Operator_A.xlsx)].[Series Title]) THEN 'OPA Remaining'

      ELSEIF

      ATTR([Series Title])=ATTR([Sheet1 (Operator_B.xlsx)].[Series Title]) THEN 'OPB Remaining'

       

      ELSE 'Series Complete'

      END

       

      This works at comparing 3 Excel docs, by seeing if there are any matching fields in Series Title column and then applying on of the 3 status. However this only works if there IS the series title in the main doc and one of the OP docs. if the series title is not in the main doc and in on of the OP docs then it just ignores it

       

      What i need to add to this, if possible, is something like, IF it IS in the OP but NOT in the main doc THEN 'Pending'

      But i have no idea how to put in the opposite of =? < or > dont work as its string not values.. or is there another way to do this all together?

       

      im so lost!

        • 1. Re: DOES NOT =
          Shawn Wallwork

          Ben, try using this structure:

           

          IF x=y AND NOT y=z THEN 'A'

           

          --Shawn

          • 2. Re: DOES NOT =
            Ben Shirley

            Hi Shawn,

             

            Thank you for getting back to me,

             

            i have been trying to workout what you mean with the structure above, am i too add this to my existing CALC, or re-build the whole field from scratch. I have kinda tried both, but one does nothing and the other just gives the end status for all. :-s

             

            Ben

            • 3. Re: DOES NOT =
              Shawn Wallwork

              Post a sample packaged workbook and I'll see if I can work it out for you.

               

              --Shawn

              • 4. Re: DOES NOT =
                Eric McDonald

                I think you probably mean the code below. The symbol ! means NOT in comparison below. You can use <> but != is slightly more meaningful.

                 

                IF

                ATTR([Series Title]) != ATTR([Sheet1 (Operator_A.xlsx)].[Series Title]) THEN 'OPA Remaining'

                ELSEIF

                ATTR([Series Title]) != ATTR([Sheet1 (Operator_B.xlsx)].[Series Title]) THEN 'OPB Remaining'

                 

                ELSE 'Series Complete'

                END

                • 5. Re: DOES NOT =
                  Ben Shirley

                  Hi Eric,

                   

                  I tried that, but i think because of the ATTR, any NULL fields are ignored by the calculation. so all i end up with is the final ELSE, in this case Series Complete.

                   

                  i think i need the ATTR though as i am cross referencing 2+ Excel Docs..

                   

                  Thanks,

                  Ben

                  • 6. Re: Re: DOES NOT =
                    Ben Shirley

                    Hi Shawn,

                     

                    Please see demo work book attached,

                    As you can see it shows Series that are "DONE", series that are half done (IE on 2 of the Excel Docs), but it does not show ones that have not been started (Pending)

                    (in this case the imaginative series of "Who Loves Coffee" and "Generic Office Stuff") I would like these to show on the same graph but under Pending or OP A pending and OP B Pending etc..

                     

                    I hope that makes sense?

                     

                    Thanks,

                    Ben

                    • 7. Re: DOES NOT =
                      Ben Shirley

                      Hi Shawn

                       

                      I'm guessing i am asking the impossible then? no worries, thanks for looking.

                      Ill see if there is another way around the issue.

                       

                      Thanks.

                      • 8. Re: DOES NOT =
                        Jonathan Drummey

                        Hi Ben,

                         

                        You wrote:


                        This works at comparing 3 Excel docs, by seeing if there are any matching fields in Series Title column and then applying on of the 3 status. However this only works if there IS the series title in the main doc and one of the OP docs. if the series title is not in the main doc and in on of the OP docs then it just ignores it

                         

                        This is exactly how data blending works in Tableau. The dimensions from the primary data source set the granularity (level of detail) of the view (by view I mean Rows, Columns, Pages, and Marks Card). Dimension values from the secondary data source cannot increase the level of detail of the view. If a linking dimension value is in a secondary source but not in the primary, Tableau will ignore that value from the secondary.

                         

                        To explain in more detail, here's what Tableau does as far as I've been able to determine:

                         

                        -Generates a query on the primary data source, using the dimensions in the view in the GROUP by clause to aggregate the measures.

                        -Generates a query on the secondary data source, using the linking dimensions to generate a GROUP by clause to aggregate the measures from the secondary.

                        - Blends the data together along the linking dimensions, any tuples (results from the query) from the secondary that don't exist in the primary are ignored. Any tuples in the primary that don't exist in the secondary will end up with measure values of Null.

                        - If the set of linking dimensions is different than set of dimensions in the view, then Tableau performs its own re-aggregation of the measures from secondary sources to the level of detail of the dimensions in the view.

                         

                        In order to get the results you want, you'll need to do some work in the data connection or outside of Tableau. For example, you could left join the A and B sheets to the primary so you'd have a single set of values. If those Excel workbooks have to be kept separate, you can create linked worksheets to have a federated data source.

                         

                        Jonathan

                        • 9. Re: DOES NOT =
                          Ben Shirley

                          Hi Jonathan

                           

                          I think i got you, so because their is a link to all 3 sheets via client then it will only show the values that match via client and my calculation, thus ignoring the ones on the sub sheets as there is not match on the main sheet.

                          interesting.

                           

                          I like you idea about a blended data source, that could work i guess. ill see what i can come up with.

                           

                          Cheers.

                          B

                          • 10. Re: DOES NOT =
                            Jonathan Drummey

                            I think you've got the essentials. Good luck, and post again if you have any questions!

                             

                            Jonathan