2 Replies Latest reply: Jul 29, 2013 11:54 AM by Kaushik Solanki RSS

    Problem with Multiple if Conditions

      Hi Friends,

       

       

      My requirement is

       

      1. I have Two fields BoxNumber AND CommissionNumber

      one Commission Number Has Multiple BoxNumber

       

      First i need to Dervie the count of BoxNumberFrom the Commission Numer.

      How do i do this?

       

      2. After Deriving the BoxNumber for the particular Commission Number  that needs to be used it in the expression

       

      if((isnull(unloadingDate)=0 and Box_Flag='Y')and isnull(Lineissuedate)= -1 and subrea <> 'Line' and Location = 'STOREHOUE' )

       

      Then i need to get the count of BoxNumber which is derived from the commission Number i Have mentioned....so how can i formulate the above conditions in

      set analysis or exact if expression?

      I am using all the Expressions in Pivot

       

       

       

       

       

      Thanks in Advance

      Nikitha Jain

        • Re: Problem with Multiple if Conditions
          Gopinathan L

          Hi,

           

          1.U can use aggr function to get the count of box numbers like  aggr(count(box_no),commission_no)

           

          for second one, i need some more clarifications.

           

          try to upload some sample data and explain the if condition where u want to use this count of box numbers.

          • Re: Problem with Multiple if Conditions
            Kaushik Solanki

            Hi Nikitha,

             

                 First of all create a flag which will tell you whether unloading date and Lineissuedate is null or not.

             

                 Something like shown below.

             

                 Load *,

                  If(Isnull(Lineissuedate),1,0) as Lineissuedate_Isnull_Flag,

                  If(Isnull(LoadingDate),1,0) as LoadingDate_Isnull_Flag

                  From xyz.

             

             

                 Then use the below expression to find the count of Boxnumber with the said conditions.

             

                 Sum(Aggr(Count({<subrea -= {'Line'},Location = {'STOREHOUE'},Lineissuedate_Isnull_Flag = {"0"},LoadingDate_Isnull_Flag = {"0"}, Box_Flag={'Y'}>}BoxNumber),CommissionNumber))

             

            Regards,

            Kaushik Solanki