Tricky conditional sum in Scripting

[markgraham123]

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,	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

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,	3	1	1	3
12345,	27865,	1	3	6	7
10908,	109566,	3	3	4	1
10908,	89678,	2	2	3	1
100876,	1000555,	1	6	6	6
145677,	167856,	2	2	9	0

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

[tamilarasu]

Hi Mark,

Check the attachment and let me know.

[markgraham123]

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!

[tamilarasu]

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.

[markgraham123]

Hi,

This is really intellectual and awesome.

You just nailed it.

Really great sir.

I'm trying it now and i will reply back

[tamilarasu]

Sure. If you have any questions, let me know,

[jonathandienst]

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,

];

[markgraham123]

Hi Tamil Nagaraj,

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 Number	Secondary Number	A to B	B to C	C to D	Final Transit	Type
12345	55677	3	1	1	2	Special
12345	27865	1	3	6	7	Regular

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

[markgraham123]

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 Number	Secondary Number	A to B	B to C	C to D	Final Transit	Type
12345	55677	3	1	1	2	Special
12345	27865	1	3	6	7	Regular

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

[tamilarasu · Accepted solution]

Mark,

Check the attachment.