1 2 Previous Next 24 Replies Latest reply on Dec 20, 2016 7:50 AM by Joe Oppelt

    Wrong Values in Table

    sherry.shaik

      Hi all,

      In attached workbook, several attributes are categorized into 5 types. I could see the correct count of values for each category in 'Bars Worksheet' but couldn't see that exact thing in Just table format.

      Any help is highly appreciated.

      Shinichiro Murakami Mahfooj Khan

       

      Thanks.

        • 1. Re: Wrong Values in Table
          Shinichiro Murakami

          If possible, could you reduce the file size with data filter with keeping failure issue.

           

          Hesitating download >10MB file.

          2MB is the max.

           

          Thanks,

          Shin

          • 2. Re: Wrong Values in Table
            sherry.shaik

            Hope this works.

            Thanks

            • 3. Re: Wrong Values in Table
              Shinichiro Murakami

              Nobody has time to analyze such a formula, sorry.

              And don't mention someone on your question.

               

              Anyways, what do you want to accomplish by this?

              Most likely, you took the issue too much complicated.

              Or you need to create one more columns in excel  or database to cover this formula functions.

              Try to do this on Tableau is bad idea.

               

              Thanks,

              Shin

              IF [mdm_description] = [mdm_description (custom_sql_query1)] AND NOT ISNULL([mdm_description])

              AND NOT ISNULL([mdm_description (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [mdm_description] != [mdm_description (custom_sql_query1)] AND NOT ISNULL([mdm_description])

              AND NOT ISNULL([mdm_description (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([mdm_description])

              AND ISNULL([mdm_description (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([mdm_description])

              AND NOT ISNULL([mdm_description (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([mdm_description])

              AND ISNULL([mdm_description (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSEIF [mdm_material_type] = [Material_type_conversion] AND NOT ISNULL([mdm_material_type])

              AND NOT ISNULL([Material_type_conversion])

              THEN "Matches"

              ELSEIF [mdm_material_type] != [Material_type_conversion] AND NOT ISNULL([mdm_material_type])

              AND NOT ISNULL([Material_type_conversion])

              THEN "Mismatches"

              ELSEIF ISNULL([mdm_material_type])

              AND ISNULL([Material_type_conversion])

              THEN "Both Nulls"

              ELSEIF ISNULL([mdm_material_type])

              AND NOT ISNULL([Material_type_conversion])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([mdm_material_type])

              AND ISNULL([Material_type_conversion])

              THEN "MDD is NULL"

              ELSEIF [Base unit_ conversion] = [mdm_base_unit (custom_sql_query1)] AND NOT ISNULL([Base unit_ conversion])

              AND NOT ISNULL([mdm_base_unit (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [Base unit_ conversion] != [mdm_base_unit (custom_sql_query1)] AND NOT ISNULL([Base unit_ conversion])

              AND NOT ISNULL([mdm_base_unit (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([Base unit_ conversion])

              AND ISNULL([mdm_base_unit (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([Base unit_ conversion])

              AND NOT ISNULL([mdm_base_unit (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([Base unit_ conversion])

              AND ISNULL([mdm_base_unit (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSEIF [mdm_laboratory] = [Lab office_ conversion] AND NOT ISNULL([mdm_laboratory])

              AND NOT ISNULL([Lab office_ conversion])

              THEN "Matches"

              ELSEIF [mdm_laboratory] != [Lab office_ conversion] AND NOT ISNULL([mdm_laboratory])

              AND NOT ISNULL([Lab office_ conversion])

              THEN "Mismatches"

              ELSEIF ISNULL([mdm_laboratory])

              AND ISNULL([Lab office_ conversion])

              THEN "Both Nulls"

              ELSEIF ISNULL([mdm_laboratory])

              AND NOT ISNULL([Lab office_ conversion])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([mdm_laboratory])

              AND ISNULL([Lab office_ conversion])

              THEN "MDD is NULL"

              ELSEIF [z_source_sector] = [z_source_sector_ conversion] AND NOT ISNULL([z_source_sector])

              AND NOT ISNULL([z_source_sector_ conversion])

              THEN "Matches"

              ELSEIF [z_source_sector] != [z_source_sector_ conversion] AND NOT ISNULL([z_source_sector])

              AND NOT ISNULL([z_source_sector_ conversion])

              THEN "Mismatches"

              ELSEIF ISNULL([z_source_sector])

              AND ISNULL([z_source_sector_ conversion])

              THEN "Both Nulls"

              ELSEIF ISNULL([z_source_sector])

              AND NOT ISNULL([z_source_sector_ conversion])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([z_source_sector])

              AND ISNULL([z_source_sector_ conversion])

              THEN "MDD is NULL"

              ELSEIF [product_ hierarcy_ conversion] = [z_bravo_minor (custom_sql_query1)] AND NOT ISNULL([product_ hierarcy_ conversion])

              AND NOT ISNULL([z_bravo_minor (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [product_ hierarcy_ conversion] != [z_bravo_minor (custom_sql_query1)] AND NOT ISNULL([product_ hierarcy_ conversion])

              AND NOT ISNULL([z_bravo_minor (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([product_ hierarcy_ conversion])

              AND ISNULL([z_bravo_minor (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([product_ hierarcy_ conversion])

              AND NOT ISNULL([z_bravo_minor (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([product_ hierarcy_ conversion])

              AND ISNULL([z_bravo_minor (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSEIF [z_catalog_number] = [mdm_base_unit (custom_sql_query1)] AND NOT ISNULL([z_catalog_number])

              AND NOT ISNULL([z_catalog_number (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [z_catalog_number] != [z_batch_management (custom_sql_query1)] AND NOT ISNULL([z_catalog_number])

              AND NOT ISNULL([z_catalog_number (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([z_catalog_number])

              AND ISNULL([z_catalog_number (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([z_catalog_number])

              AND NOT ISNULL([z_catalog_number (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([z_catalog_number])

              AND ISNULL([z_catalog_number (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSEIF [z_parent_code] = [z_parent_code (custom_sql_query1)] AND NOT ISNULL([z_parent_code])

              AND NOT ISNULL([z_parent_code (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [z_parent_code] != [z_parent_code (custom_sql_query1)] AND NOT ISNULL([z_parent_code])

              AND NOT ISNULL([z_parent_code (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([z_parent_code])

              AND ISNULL([z_parent_code (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([z_parent_code])

              AND NOT ISNULL([z_parent_code (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([z_parent_code])

              AND ISNULL([z_parent_code (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSEIF [z_batch_management] = [mdm_base_unit (custom_sql_query1)] AND NOT ISNULL([z_batch_management])

              AND NOT ISNULL([z_batch_management (custom_sql_query1)])

              THEN "Matches"

              ELSEIF [z_batch_management] != [z_batch_management (custom_sql_query1)] AND NOT ISNULL([z_batch_management])

              AND NOT ISNULL([z_batch_management (custom_sql_query1)])

              THEN "Mismatches"

              ELSEIF ISNULL([z_batch_management])

              AND ISNULL([z_batch_management (custom_sql_query1)])

              THEN "Both Nulls"

              ELSEIF ISNULL([z_batch_management])

              AND NOT ISNULL([z_batch_management (custom_sql_query1)])

              THEN "BtB is NULL"

              ELSEIF NOT ISNULL([z_batch_management])

              AND ISNULL([z_batch_management (custom_sql_query1)])

              THEN "MDD is NULL"

              ELSE NULL

              END

              • 4. Re: Wrong Values in Table
                Joe Oppelt

                Your table sheet isn't separating the rows by [SELECT].  So Tableau is adding up things only by [Calculation 1].  The sum of the totals on the two sheets are the same.  But you're just looking at things through different "cuts" of the data between the two sheets.

                • 5. Re: Wrong Values in Table
                  Joe Oppelt

                  Wow, Shin.  I totally missed that [Calculation1] is attempting to create the same "chunks" that [SELECT] does. 

                   

                  And you're right.  I don't have the time to debug that either.

                  • 6. Re: Wrong Values in Table
                    sherry.shaik

                    The above formula is comparing and categorizing all the attributes into 5 types.

                     

                    IF Field1= Field2 AND NOT ISNULL(Field1)

                    AND NOT ISNULL(Field2)

                    THEN "Matches"

                     

                    ELSEIF Field1!= Field2 AND NOT ISNULL(Field1)

                    AND NOT ISNULL(Field2)

                    THEN "Mismatches"

                     

                    ELSEIF ISNULL(Field1)

                    AND ISNULL(Field2)

                    THEN "Both Nulls"

                     

                    ELSEIF ISNULL(Field1)

                    AND NOT ISNULL(Field2)

                    THEN "Field1 is NULL"

                     

                    ELSEIF NOT ISNULL(Field1)

                    AND ISNULL(Field2)

                    THEN "MDD is NULL"

                     

                    I have seen some other posts where they were tagging Data Monarch's, Hence i thought that as right of posting stuffs. Apologies for inconvenience.

                     

                    Thanks

                    • 7. Re: Wrong Values in Table
                      Shinichiro Murakami

                      Joe, that's right.

                      But fundamental issue is "calculation 1" I think.

                      Sherry used different but similar dimension and have different number on "same" category name under different dimensions.

                       

                      Sherry, correct me if I'm wrong and I apology for above comment if that is the case.

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: Wrong Values in Table
                        Joe Oppelt

                        But I'll say this after looking at the full printout of the calc:

                         

                        When tableau hits a TRUE condition, it stops looking at all the other ELSEIF logic.

                         

                        The "Table" sheet shows only two values being calculated.  That tells me that those first two conditions are capturing all rows.  Everything is falling into one or the other condition, and no other conditions are ever being looked at.


                        If those two conditions are the "catch-all" conditions if nothing else gets met, move them to the bottom of the IF logic so they get evaluated last.

                         

                        But that's just a very simplistic approach to fixing that giant calc if there is really some other problem in there.

                        • 9. Re: Wrong Values in Table
                          sherry.shaik

                          HI Joe,

                          What could be the possible way of getting a table that shows all attributes in 5 categories in Tableau?

                          Thanks

                          • 10. Re: Wrong Values in Table
                            Joe Oppelt

                            So you list 5 possible conditions in your example comparing field1 and field2.  My thought, then, is why is your calc so huge?

                             

                            Then I looked closer.  You are doing that series of checks successively for multiple pairs of fields.  Once tableau gets through the first 5 ELSEIFs, it is guaranteed to have hit a TRUE condition and will never go on to look at the second, and third, and 4th, etc., fields.

                            • 11. Re: Wrong Values in Table
                              sherry.shaik

                              I will definitely try and will post it here if it works.

                              Thanks Joe.

                              • 12. Re: Wrong Values in Table
                                Joe Oppelt

                                You'll need to do 5 calcs, one for each pair of fields.  Then, if there is some final value to be discerned from those 5 comparisons, do that in a 6th calc.

                                • 13. Re: Wrong Values in Table
                                  Joe Oppelt

                                  Sherry -- Scratch my first suggestion of dropping the first two ELSEIFs to the bottom.  Now I understand this better.


                                  You need to have 5 separate calcs.


                                  What if you get MISMATCH for one pair of fields, and "BOTH NULL" for another -- all in the same row, for example?

                                  • 14. Re: Wrong Values in Table
                                    sherry.shaik

                                    I have 11 fields in two tables that need to be compared and categorized in 5 possible conditions. My idea is to concatenate all 11 fields by showing their distribution in 5 conditions in just one worksheet either in 11 Stacked bars or a table format.

                                    1 2 Previous Next