6 Replies Latest reply on Aug 17, 2018 6:56 AM by Mahfooj Khan

    Calculated field

    shweta s

      Hi Folks,

       

      Is it possible to create calculated field with the following condition :

       

      I have existing join condition where C1=C2 and L1=L2 at query level but because am using left outer join. i come across data where c1<> c2 or L1<>L2 how can i avoid those data.

       

       

      C1C2L1 L2Result
      77null26null FALSE
      88882222TRUE
      nullnullnullnull TRUE
      null77null26FALSE

       

       

      Thanks

        • 1. Re: Calculated field
          Shinichiro Murakami

          HI Shweta

           

          Could you please clarify your original two individual data source and expected result after join?

           

          Thanks,

          Shin

          • 2. Re: Calculated field
            shweta s

            Hi Shin,

             

            Thanks for responding...

            I have 2 data source C1 and L1  belong to DS1 and C2 and L2 belong to DS2 I already have left outer  join  between with join condition C1=C2 and L1=L2

             

            above is the result i get on report but i want only below as output on my report

             

            C1C2L1L2Result
            88882222TRUE
            nullnullnullnull

            TRUE

             

            • 3. Re: Calculated field
              Shinichiro Murakami

              Could you please clarify your original two individual data source

              • 4. Re: Calculated field
                Mahfooj Khan

                Hi,

                 

                I don't know the structure of your data sources, however I've tried to fix your join condition to get the expected output.

                First of all using measures as join condition seems quite strange for me. You should use fields which have unique ID.

                I've assumed your data sources are like this

                 

                DS1                                                                    DS2

                         

                I've added Row ID in both the sources to identify each records. Then connect the sources

                while applying joining condition instead of left outer join you have to use inner join and most important write calculated joining condition and use IFNULL() to handle the null values as zero so that null values also meet the condition and appears in your result set. Below is the screenshot of the joining condition and output.

                workbook attached for your reference. Let us know if this help.

                 

                Mahfooj

                • 5. Re: Calculated field
                  shweta s

                  Hi,

                   

                  I want to filter out data with false value but at same time I want C1(null)=c2(null) and L1(null)=L2(Null) ad valid and true.

                   

                  • 6. Re: Calculated field
                    Mahfooj Khan

                    Could you please share some mock up data in packaged workbook?