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

# Tricky conditional sum in Scripting

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

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

• ###### Re: Tricky conditional sum in Scripting

Hi Mark,

Check the attachment and let me know.

• ###### Re: Tricky conditional sum in Scripting

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

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

Hi,

This is really intellectual and awesome.

You just nailed it.

Really great sir.

I'm trying it now and i will reply back

• ###### Re: Tricky conditional sum in Scripting

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

• ###### Re: Tricky conditional sum in Scripting

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".

• ###### Re: Tricky conditional sum in Scripting

Mark,

Check the attachment.

• ###### Re: Tricky conditional sum in Scripting

Thank you verymuch sir.

This is really a great help

• ###### Re: Tricky conditional sum in Scripting

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

• ###### Re: Tricky conditional sum in Scripting

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';

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;

• ###### Re: Tricky conditional sum in Scripting

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

Mark Graham wrote:

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';

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.

• ###### Re: Tricky conditional sum in Scripting

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.

• ###### Re: Tricky conditional sum in Scripting

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

• ###### Re: Tricky conditional sum in Scripting

Here is the attachment.

• ###### Re: Tricky conditional sum in Scripting

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.

• ###### Re: Tricky conditional sum in Scripting

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

FYI, here is the code:

ERROR:

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

Set vChara = 'P';

Table1:

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;

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:

[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;

• ###### Re: Tricky conditional sum in Scripting

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.

• ###### Re: Tricky conditional sum in Scripting

THanq very much for your tim and patience my friend.

i Really appreciate it

I learnt good concepts from u .

you were nailing it.

• ###### Re: Tricky conditional sum in Scripting

This will do it:

[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]

;

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

];

• ###### Re: Tricky conditional sum in Scripting

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".