Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Mark,
Check the attachment and let me know.
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!
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.
Hi,
This is really intellectual and awesome.
You just nailed it.
Really great sir.
I'm trying it now and i will reply back
Sure. If you have any questions, let me know,
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,
];
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".
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".
Mark,
Check the attachment.