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

[markgraham123]

Thank you verymuch sir.

This is really a great help

[tamilarasu]

Glad that you are happy. Hope you understand the concept.

[markgraham123]

Thanq friend.:)

I understood the concept.

Can i p[lease know what does this code do.

In the below if i want to search only Secondary IDs with 'P', i understand that

"Set vMatch = 'p'; doesn't work. Can i please know the working logic of this.

Set vMatch = 'X','P';

Load *,

If(Match(Right([Secondary ID],1),$(vChara)),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [New Secondary ID],

If(Match(Right([Secondary ID],1),$(vChara)),'Special','Regular') as Type;

[tamilarasu]

I just used variable to enter all the criteria at start and can reuse them wherever needed.

Mark Graham wrote:

Load *,

If(Match(Right([Secondary ID],1),$(vChara)),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [New Secondary ID],

First, i just take last character by using right function and trying to match with already defined variable vMatch. If match found, left function will exclude last letter. We can use len -1 to know the string length as we dont need last character. If no match, we need to keep the ID as it is.

Mark Graham wrote:

If(Match(Right([Secondary ID],1),$(vChara)),'Special','Regular') as Type;

In this line, i just used right function to take last letter and checking whether we have a match with defined letters (variable). If match found, then special or Regular as type. Thats all.

By the way, you forget to change the variable name at start (vMatch to vChara). Change like below and add your criteria's only in red color highlighted part.

Set vChara = 'X','P';

Load *,

If(Match(Right([Secondary ID],1),$(vChara)),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [New Secondary ID],

If(Match(Right([Secondary ID],1),$(vChara)),'Special','Regular') as Type;

Hope this is clear.

[markgraham123]

Hi Friend,

I got the logic.

But when i try to give only 'P' value in vMatch, it just says value 'P' not found.

But it works when i give two values in Set vMatch.

I was just eager to know.

[tamilarasu]

Where you got the message. Could you post the screenshot. It would be easy to explain u.

[markgraham123]

Here is the attachment.

[tamilarasu]

Sorry Mark. Im not near to my system. I will check and let you know by tomorrow morning. If its urgent, post the code here.

[markgraham123]

That's fine friend. No urgency. I was just chekcing to understand the working logic.

FYI, here is the code:

ERROR:

<P> - Not found:

But when i give Set vChara = 'P', 'P' it works!!!

Set vChara = 'P';

Table1:

Load *,

If(Match(Right([Secondary ID],1),$(vChara)),Left([Secondary ID],Len([Secondary ID])-1),[Secondary ID]) as [New Secondary ID],

If(Match(Right([Secondary ID],1),$(vChara)),'Special','Regular') as Type;

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

];

NoConcatenate

Final:

Load [Primary ID],

[New Secondary ID] as [Secondary ID],

If(WildMatch(Concat(Type, ','),'*Special*'),'Special','Regular') As Type,  //First we are concatenate same type of ID's. Then we are finding match character by using wildmatch

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]

Resident Table1

Group By [Primary ID],[New Secondary ID];

Drop Table Table1;

[tamilarasu]

Mark,

      Sorry for the late reply. I just did a check and found the issue. If you give single value then QV omits the single quotes. But Match function will work if the values are wrapped with single quotes. If you give two or more values in set part, then QV will take the single quotes. So, I would suggest you to add double quotes at start and end of the values. This is something new to me.

Set vChara = " 'P', 'X' ";  

               or

Set vChara = " 'P' ";

I have attached the screenshot for your understanding. Let me know.