20 Replies Latest reply: Nov 24, 2015 9:18 PM by Mark Graham RSS

    Tricky conditional sum in Scripting

    Mark Graham

      Hi all,

       

      I'm working on a tricky requirement where:

      I have a Primary number and secondary number.

      Every number will have a Secondary number.

      But for few Primary ID, there exists 2 secondary IDs which resembles that both are same but its corresponding data has to be summed up.

      Data:

      Primary ID,Secondary ID,A to B,B to C,C to D,Final Transit
      12345,55677,211
      12345,55677P,12
      12345,27865,1367
      10908,109566,334
      10908,109566P,1
      10908,89678,2231
      100876,1000555,1660
      100876,1000555X0006
      145677,167856,229

      Please find the application attached

       

      Requirement: For Primary ID 12345, we have Secondary IDs 55677 and 55677P.

      For 55677, A to B is 2, B to C is 1, C to D is 1

      For 55677P, A to B is 1 and Final Transit is 2.

       

      I'm looking for an output where the corresponding values should be summed up as the Secondary IDs points to same.

       

      OUTPUT:


      Primary ID,Secondary ID,A to B,B to C,C to D,Final Transit
      12345,55677,3113
      12345,27865,1367
      10908,109566,3341
      10908,89678,2231
      100876,1000555,1666
      145677,167856,2290



      For 55677,

      A to B should be 3

      B to C should be 1

      C to D should be 1 and

      Final Transit should be 2.

      Any help is highly appreciated. Help Please !!

       

      Message was edited by: Mark Graham

        • Re: Tricky conditional sum in Scripting
          Tamil Nagaraj

          Hi Mark,

           

          Check the attachment and let me know.

            • Re: Tricky conditional sum in Scripting
              Mark Graham

              Hi Mr. Tamil Nagaraj,

               

              This is perfect.

              This is what i was looking for.

               

               

              But can you please explain what does these statements do???

               

              If(Match(Right([Secondary ID],1),'X'),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [Secondary ID],

              and

               

              Group By [Primary ID],if(match(right([Secondary ID],1),'X'),Left([Secondary ID],len([Secondary ID])-1),[Secondary ID]);

               

              I'm trying this. I will let you know!

                • Re: Tricky conditional sum in Scripting
                  Tamil Nagaraj

                  Hi Mark,

                  Mark Graham wrote:

                   

                  If(Match(Right([Secondary ID],1),'X'),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [Secondary ID],

                  Match(Right([Secondary ID],1),'X','P')  - First, we are taking last letter by using right function. Checking whether the last letter is X or P by using Match function. If the match is true, then we are using the below formula to remove the last letter.


                  Left([Secondary ID],Len([Secondary ID])-1) - we are taking the string length (len function) and subtracting 1 from the length.


                  If the match is false, we are directly taking [Secondary ID] column value.

                  Mark Graham wrote:

                   

                  Group By [Primary ID],if(match(right([Secondary ID],1),'X'),Left([Secondary ID],len([Secondary ID])-1),[Secondary ID]);


                  Here, we are using aggregation function (Sum) to sum the values [A to B] [B to C] etc. So, we have to use group by statement. We have done some amendments in [Secondary ID] column (See above). Hence, I just mention the formula directly. So that grouping will work as expected.


                  I just modified the attachment for you. Check again.


                  Hope this clear.


              • Re: Tricky conditional sum in Scripting
                Jonathan Dienst

                This will do it:

                 

                LOAD

                  [Primary ID],

                  Concat([Orig Secondary ID], ',') As [Orig Secondary IDs],

                  [Secondary ID],

                  Sum([A to B]) As [A to B],

                  Sum([B to C]) As [B to C],

                  Sum([C to D]) As [C to D],

                  Sum([Final Transit]) As [Final Transit]

                Group By [Primary ID], [Secondary ID]

                ;

                LOAD

                  [Primary ID],

                  [Secondary ID] As [Orig Secondary ID],

                  PurgeChar([Secondary ID], 'PXpx') As [Secondary ID],

                  [A to B],

                  [B to C],

                  [C to D],

                  [Final Transit]

                Inline

                [

                  Primary ID, Secondary ID, A to B, B to C, C to D, Final Transit

                  12345,  55677, 2, 1, 1,

                  12345,  55677P, 1,,, 2

                  12345,  27865, 1, 3, 6,7

                  10908,  109566, 3, 3, 4,

                  10908,  109566P,,,,1

                  10908,  89678, 2, 2, 3, 1

                  100876, 1000555, 1, 6, 6, 0

                  100876, 1000555X, 0, 0, 0, 6

                  145677, 167856, 2, 2, 9,

                ];


                t1.png

                  • Re: Tricky conditional sum in Scripting
                    Mark Graham

                    Hi Jonathan Dinest,

                     

                    I woulds like to add a new dimension "Type" in the data set which says "Special" for the ones we have manipulated.

                     

                    For EG:

                     

                    Primary NumberSecondary NumberA to BB to CC to DFinal TransitType
                    12345556773112Special
                    12345278651367Regular

                    Since, the secondary number 55677 had extra character, i would like to put it as an "Special Type".