1 2 Previous Next 15 Replies Latest reply on Sep 3, 2018 5:05 PM by Chandu p

    Columns data in to a single row

    Chandu p

      How to bring columns data in to a single row. Based on these fields I need to write calculated fields.My logic is not working properly because of improper alignment of data.

      Is it possible to do in Tableau to bring action1, action2, action3 column data to a single row next to code.

      Capture 1.JPG

        • 1. Re: Columns data in to a single row
          Naveen B

          Hi Chandu,

           

          Click on Action1,2 and 3 Pivot the data so that all the values will be tagged under the single column

           

          Check this link for how to pivot : Pivot Data from Columns to Rows

           

          Hope this helps

           

          Plz mark it helpful and correct to close the thread

           

          BR,

          NB

          • 2. Re: Columns data in to a single row
            Deepak Rai

            Hi Chandu,

            There are 2 ways to do this, either use Tableau Prep or You can use Tableau To Pivot it. I used both ways and got this Reslt.

            Thanks

            Deepak

            If It Helps, Pl Mark It Helpful and CORRECT to Close Thread

            • 3. Re: Columns data in to a single row
              Okechukwu Ossai

              Hi Chandu,

               

              What you probably need in this case is just a calculated field to bring the dimensions together. See attached workbook.

               

              One calculated field will do it.

               

              However, if you also want to show where the rows are coming from, then do this;

               

               

              Hope this helps.

              Ossai

              • 4. Re: Columns data in to a single row
                Chandu p

                Hello Deepak,

                 

                I got this Pivot option if I connect to the excel file but These actions columns are calculated fields in tableau and not getting option to pivot the data. How to do in this case?

                • 5. Re: Columns data in to a single row
                  Deepak Rai

                  Please follow the Solution from Mr. Ossai as above.

                  • 6. Re: Columns data in to a single row
                    Chandu p

                    Hi Ossai,

                     

                    This sounds good but is any way to bring these action type column in to rows. I need to show it in rows based on each ID and code.

                    • 7. Re: Columns data in to a single row
                      Okechukwu Ossai

                      Hi Chandu,

                       

                      Is this what you mean by bringing the action type columns to rows?

                       

                       

                       

                      Hope this helps.

                      Ossai

                      1 of 1 people found this helpful
                      • 8. Re: Columns data in to a single row
                        Chandu p

                        Wow Ossai You are awesome.Appreciate your help.

                        I need to calculate three more columns based on this data.Can you please help with this.

                        Total chart count based on Action1,Action2,Action3 - if all actions are null then 0.It means any action Passed from one level to another level count should be 1 else 0. This need to be calculated on ID level.

                        Chart Error- Any actions are not matching with all three levels then count of charts with errors on ID level.

                        code error - when comparing b/w action1 and action2 then if action1=action2 then 0 else 1

                        when action3 is not null then compare action1 ignore action2 and if action1=action3 then 0 else 1

                        This column is based on code level and need to compare only with codes.

                         

                         

                         

                        • 9. Re: Columns data in to a single row
                          Okechukwu Ossai

                          Hi Chandu,

                           

                          Below is an attempt to translate your logic into calculated fields. I may not fully understand what the logic means, so show me with example what the expected result should be if this is not what you are looking for. See attached workbook.

                           

                          Please mark my answer as helpful/correct if it resolved your question.

                           

                          Ossai

                           

                           

                           

                          1 of 1 people found this helpful
                          • 10. Re: Columns data in to a single row
                            Chandu p

                            Hello Ossai,

                             

                            I need these calculations in code error column.

                             

                            What I am looking is when action1 status is Delete DOS followed by any status flag that should be match with action 2 and action 3 .If not then show it as

                            code error. This is only when action starts with Delete DOS status flag. These flags are pulling from separate column.

                            Remaining all status flags( confirm,delete,new add, auto new add..etc) need to compare with three actions in this same field as per the code error calculation you wrote.

                             

                            This is a valid case and error is zero.

                            img4.JPG.

                            In Below Image code I743 has Delete DOS followed by another status flag which is matching with all actions but not matching with first status, so error is 1

                            I 771-  error is 0

                            img1.JPG

                            • 11. Re: Columns data in to a single row
                              Okechukwu Ossai

                              Hi Chandu,

                               

                              Thanks for the clarification. I know you gave examples with F315 and I743. However, few things are still not clear to me. I already have ideas on what the solution might be but I need to make sure I'm on the right path. So, below is a screenshot from my previous solution. Can you let me know what the Code error should be for every row in the image below. I just need the result (1 or 0). Don't worry about the logic, I'll work that out if you tell me what the expected result should be for every line.

                               

                               

                              Ossai

                              1 of 1 people found this helpful
                              • 12. Re: Columns data in to a single row
                                Chandu p

                                Hello Ossai,

                                 

                                Thank you very much for your response. Here are the results for all codes.Hope this makes you clear.

                                 

                                E119-  Action 3 Null and action 1 and action 2 same so error 0

                                E1122 - Each ID can have different codes but each code won't consist of different ID - may be it's a bad data but in this case 1 error as per this code

                                E1139- 1

                                E1152-0

                                F320-0

                                G20-0

                                G460-0

                                G6281-1

                                I96-1 ( Action 3 is null but when compared to 1 and 2 status flag is different, so error is 1)

                                I700-0

                                I739-0

                                J449-0

                                M069-0

                                M0600-0

                                N183-0

                                Z6825-1

                                Z6826-1

                                Z6829-0

                                Z6830-1( Even though two status flags only one code error because it's a Delete DOS flag- As explained in my last post- Delete DOS always follows another code in each level )

                                 

                                Condition:

                                when action1 status is Delete DOS followed by any status flag that should be match with action 2 and action 3 .If not then show it as

                                code error. This is only when action starts with Delete DOS status flag. F-315 is the best example with 0 error

                                 

                                Along with above condition  need to include given condition. Both conditions should be in same calculative field.

                                 

                                Screen Shot 2018-08-29 at 10.29.39 PM.png

                                • 13. Re: Columns data in to a single row
                                  Okechukwu Ossai

                                  Hi Chandu,

                                   

                                  I've broken the logic into two major parts; when a code has one ID and when it has more than 1 ID. This should be enough to give the correct result I believe. Let me know how it goes. My only concern is I771 which you mentioned in your other question. The logic appears different from what you provided here.

                                  I hope this is going to resolve the issue but if not please attach a workbook with sample data. It's difficult building a logic from an image.

                                  See attached workbook.

                                   

                                  Ossai

                                   

                                   

                                  1 of 1 people found this helpful
                                  • 14. Re: Columns data in to a single row
                                    Chandu p

                                    Thank you Ossai, Thanks a Ton. I will implement same and will check.Hope this will work fine.Will get back to you

                                    1 2 Previous Next