15 Replies Latest reply on Dec 12, 2016 5:54 PM by Shinichiro Murakami

    Calculation error

    sherry.shaik

      Hi all,

      I'm working on 2 different Data sources. I am struck at a calculation which is supposed to be like

      IF A=B AND

      B=C AND

      C=D AND

      E=F THEN

      'MATCH' ELSE

      'MISMATCH'

      END

       

      In the above calculation we have E and F in secondary Data Source. The error is ''ALL FIELDS MUST BE AGGREGATE OR CONSTANT, WHEN USING TABLE CALCULATION FUNCTIONS OR FIELDS FROM MULTIPLE DATA SOURCES''. All the fields are in dimensions and none of the table calculation functions are used. I ain't getting any error if i use ATTR(E)=ATTR(F) but i still don't understand why it's showing error in the above calculation.

      P.S: All the data types are in string format.Joe OppeltSrinidhi NarayanBarry PlumThe specified item was not found.@

        • 1. Re: Calculation error
          Shinichiro Murakami

          You need to aggregate "ALL" the field when combine two data set's field in formula.

           

          min/sum does not matter in this case.

           

          [Match / Unmatch]

          if min([A])=min([B])

          and  min([A])=min([C])

          and min([EF].[E])=min([A])

          and min([EF].[F])=min([A])

          then "Match" else "Not Match" end

           

           

          One reqies to you.

          Pleae attach packaged workbook even in case of simple data set.

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Calculation error
            sherry.shaik

            Tab.PNG

            In the selected region of the calculation window i couldn't enter dimension from another Data source server(GTIN_EXCLN_NM in MDM Database). Even if we convert the Review category into Aggregates how are we going to show that in the table format like in the displayed worksheet?

            • 3. Re: Calculation error
              Shinichiro Murakami

              One request to you.

              Please attach packaged workbook even in case of simple data set.

              • 4. Re: Calculation error
                sherry.shaik

                Apologies for wrong attachment PFA Shinichiro Murakami

                • 5. Re: Calculation error
                  Shinichiro Murakami

                  Unfortunately, your datasource requires pswd to access.

                  Could you extract data and attach packaged workbook.

                   

                   

                  Packaged workbooks: when, why, how

                   

                  Thanks,

                  Shin

                  • 6. Re: Calculation error
                    sherry.shaik

                    I edited the attachment.

                    • 7. Re: Calculation error
                      Shinichiro Murakami

                      Sorry, I don't have time and power to analyze such as formula....

                       

                       

                      IF [gtininmdmprofile]='GTIN_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_In_MDM'

                      THEN

                      "GTIN MDM MATCH"

                      ELSEIF

                      [gtininmdmprofile]='GTIN_NOT_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_In_MDM'

                      THEN

                      "PROFILE NO GTIN"

                      ELSEIF [gtininmdmprofile]='GTIN_NOT_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_NOT_In_MDM' AND

                      [parnt_prod_cd_match_status]='ITEM_NUMBER_MATCHES_WITH_PARNT_PRODUCT'

                      THEN "PROFILE AT PARENT NO GTIN"

                      ELSEIF [gtininmdmprofile]='GTIN_NOT_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_In_MDM' AND

                      [parnt_prod_cd_match_status]='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                      [productregistraion]='REGISTERED_IN_MDM' THEN

                      "PRODUCT IN MDM WITH GTIN EXCLUSIONS"

                      ELSEIF [gtininmdmprofile]='GTIN_NOT_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_NOT_In_MDM' AND

                      [parnt_prod_cd_match_status]='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                      [productregistraion]='REGISTERED_IN_MDM' THEN

                      "PRODUCT IS REGISTERED IN MDM"

                      ELSEIF [gtininmdmprofile]='GTIN_NOY_IN_MDM_PROFILE' AND

                      [mdm_profile_info]='Profile_NOT_In_MDM' AND

                      [parnt_prod_cd_match_status]='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                      [productregistraion]='NOT_REGISTERED_IN_MDM' THEN

                      "PRODUCT IS NOT REGISTERED IN MDM"

                      ELSE 'NULL'

                      END

                       

                       

                      Thanks,

                      Shin

                      • 8. Re: Calculation error
                        sherry.shaik

                        It's a categorization of a GTIN number with reference to different dimensions in ems_core and Mdm_data. I'm almost at the end of the requirement but stuck in taking GTIN_EXCLN_NM dimension from MDM_data("ELSEIF [GTIN_EXCLN_NM]='Null' THEN 'NO EXCLUSIONS')

                        I appreciate your patience in trying to understand the issue.

                        • 9. Re: Calculation error
                          Shinichiro Murakami

                          Finally found.

                           

                          Remove secondary data dimension from table.  That's the key.

                          Before that, add attr() for all the fields.

                           

                          I really appreciate my patience.

                           

                           

                           

                           

                          [Review Category]

                          IF attr([gtininmdmprofile])='GTIN_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_In_MDM'

                          THEN

                          'GTIN MDM MATCH'

                          ELSEIF

                          attr([gtininmdmprofile])='GTIN_NOT_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_In_MDM'

                          THEN

                          'PROFILE NO GTIN'

                          ELSEIF attr([gtininmdmprofile])='GTIN_NOT_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_NOT_In_MDM' AND

                          attr([parnt_prod_cd_match_status])='ITEM_NUMBER_MATCHES_WITH_PARNT_PRODUCT'

                          THEN 'PROFILE AT PARENT NO GTIN'

                          ELSEIF attr([gtininmdmprofile])='GTIN_NOT_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_In_MDM' AND

                          attr([parnt_prod_cd_match_status])='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                          attr([productregistraion])='REGISTERED_IN_MDM' THEN

                          'PRODUCT IN MDM WITH GTIN EXCLUSIONS'

                          ELSEIF attr([gtininmdmprofile])='GTIN_NOT_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_NOT_In_MDM' AND

                          attr([parnt_prod_cd_match_status])='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                          attr([productregistraion])='REGISTERED_IN_MDM' THEN

                          'PRODUCT IS REGISTERED IN MDM'

                          ELSEIF attr([gtininmdmprofile])='GTIN_NOY_IN_MDM_PROFILE' AND

                          attr([mdm_profile_info])='Profile_NOT_In_MDM' AND

                          attr([parnt_prod_cd_match_status])='ITEM_NUMBER_DOES_NOT_MATCH_WITH_PARNT_PRODUCT' AND

                          attr([productregistraion])='NOT_REGISTERED_IN_MDM' THEN

                          'PRODUCT IS NOT REGISTERED IN MDM'

                          else IFNULL(ATTR([(MDM_DATA)].[GTIN_EXCLN_NM]),'NO EXCLUSIONS')

                           

                           

                          END

                           

                           

                          Thanks,

                          Shin

                          1 of 1 people found this helpful
                          • 10. Re: Calculation error
                            sherry.shaik

                            Thank You So  Much Shinichiro Murakami.
                            Is there anyway that we can create them in Dimension instead of aggregated field?

                            • 11. Re: Calculation error
                              Shinichiro Murakami

                              Don't know technical background.

                              Just guessing it's conflicting when try to put secondary data field on dimension and measure at the same time.

                               

                              Thanks,

                              Shin

                              1 of 1 people found this helpful
                              • 12. Re: Calculation error
                                sherry.shaik

                                Is there a way to create a bar chart with number of faseproduct# with respect to Review Category?
                                When i tried to create, it's taking everything as NULL...

                                • 13. Re: Calculation error
                                  Shinichiro Murakami

                                  Keep "faceprod" field in the detail.

                                   

                                   

                                  Thanks,

                                  Shin

                                  1 of 1 people found this helpful