# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor II

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

Tags (3)
1 Solution

Accepted Solutions
MVP

## Re: Tricky conditional sum in Scripting

Mark,

Check the attachment.

20 Replies
MVP

## Re: Tricky conditional sum in Scripting

Hi Mark,

Check the attachment and let me know.

Contributor II

## 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!

MVP

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

Contributor II

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

MVP

## Re: Tricky conditional sum in Scripting

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

MVP

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

];

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Contributor II

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

Contributor II

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

MVP

## Re: Tricky conditional sum in Scripting

Mark,

Check the attachment.