8 Replies Latest reply on Mar 4, 2013 11:29 PM by Prashant Sharma

    Same ID on different rows having different values

    Prashant Sharma

      Hi,

      In Id column, some ID's are repetitive but having different values in other columns depend on one other column's value("B" is the column name). In Tableau i created different columns depend upon the condition put on column "B". Following is the Main Table present in Database: -

       

      ID    B     C

      1     a     2/11/2012

      2     b     2/23/2012

      2     c     2/2/2012

      3     b     2/2/2012

      4     a     2/15/2012

      5     b     2/18/2012

      5     a     2/13/2012

       

      Now i have to create columns like: -

      Column D: - Show C when B is a otherwise blank value not "null"

      Column E: - Show C when B is c otherwise blank value not "null"

      Column F: - Show C when B is b otherwise blank value not "null"

       

      But when i created report in Tableau it gives me: -

       

      ID               D                E            F    

      1         2/11/2012          null          null

      2          null                  null      2/23/2012

                                    2/2/2012       null

      3          null                null        2/2/2012

       

       

      But what i want is different: -

       

      ID                D                    E                F

      1          2/11/2012                  

      2                                   2/2/2012         2/23/2012

      3                                                        2/2/2012

       

      Thanks in Advance

        • 1. Re: Same ID on different rows having different values
          Dana Withers

          Hello,

           

          I may misunderstand the problem, but if you don't want to see the NULL can you not just put in a empty string?

          If you want this displayed as a text table in the way you shown, it does not matter if you make it text.

          For example: iif([B]="a",str([C]),"")

           

          If you want to calculate with it, then you would really need the Nulls to make sure calculations are not upset by empty values. Perhaps you could create different calculated fields for when you just need a date display or a date to calculate with and work around it like that?

           

          Hope this helps !

           

          Dana

          1 of 1 people found this helpful
          • 2. Re: Same ID on different rows having different values
            Prashant Sharma

            Thanks Dana,

            My problem of  not showing null values are solved now as per your given solution but i do not want data in two rows, I wanted data in only single row. After your solution result is as follows: -

             

            ID               D                E            F   

            1         2/11/2012                   

            2                                              2/23/2012

                                          2/2/2012      

            3                                                 2/2/2012

             

            But what i want is like this: -

            ID                D                    E                F

            1          2/11/2012                 

            2                                   2/2/2012         2/23/2012

            3                                                        2/2/2012

            • 3. Re: Same ID on different rows having different values
              Dana Withers

              Hello,

               

              In that case, why do you need to create different columns for it ?

              Why not put row B in the "columns" shelf, C in the text bit and let it sort itself out ?

              That way any empty values are simply shown as empty fields and IDs with multiple dates are automatically grouped on one line.

              See screenshot.

               

              If the naming of a, b and c that are in your "B" field is a problem, then you can give them aliasses of D, E and F.

               

              Hope that helps,


              Dana

              • 4. Re: Same ID on different rows having different values
                Prashant Sharma

                Thank you Dana,

                 

                This worked for me but i used created columns because now i want date range of three date columns & i think this is not possible in your solution. As, now i want D, E & F as three different date ranges filter but in your solution if i used D as date range filter then E & F are not visible. So, if you have any solution for this then please provide me. I appreciate your efforts.

                • 5. Re: Same ID on different rows having different values
                  Dana Withers

                  Hi Prashant,

                   

                  Please see packaged workbook attached.

                  I've used a combination of all discussed

                   

                  First I've created the table by setting ID on Rows, B on Columns (displaying 3 columns called a, b and c) and C (day level) on text. That way I get the table you wanted to see with grouped dates.

                  Then I've aliased the values for a, b and c into D, E and F so that it matches what you actually showed in your example.

                  After that, I've created 3 calculated fields D Filter, E Filter and F Filter (the "Filter" is just to avoid confusion with the aliased column names for B, but if you were to give this to a user and name it the same, they'll never know it is not the same fields at all). Each of those fields is only filled with a date if their value in column B is matching and is filled with NULL if not.

                  Now I can create a filter for each and give them a date range (make sure you tick "include Nulls") so that you can filter each separately from the others. It helps to set the filter range manually or each filter will start and end at different dates depending on the values in their column

                  You'll see that only correct values are removed.

                   

                  Hope that helps ?

                   

                  Dana

                  1 of 1 people found this helpful
                  • 6. Re: Same ID on different rows having different values
                    Prashant Sharma

                    Hi Dana,

                    Please also provide the data file you created as i am unable to open your solution without the data source you created.

                    Thanks

                    • 7. Re: Same ID on different rows having different values
                      Dana Withers

                      Ach... I'm a daftie!

                      Sorry here is the packaged workbook

                      • 8. Re: Same ID on different rows having different values
                        Prashant Sharma

                        Thanks Dana,

                         

                        This is exactly what i wanted. I am really appreciate your efforts on this. Thanks Again.