Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
markgraham123
Valued Contributor

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,211
12345,55677P,12
12345,27865,1367
10908,109566,334
10908,109566P,1
10908,89678,2231
100876,1000555,1660
100876,1000555X0006
145677,167856,229

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,3113
12345,27865,1367
10908,109566,3341
10908,89678,2231
100876,1000555,1666
145677,167856,2290



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

1 Solution

Accepted Solutions

Re: Tricky conditional sum in Scripting

Mark,

Check the attachment.

View solution in original post

20 Replies

Re: Tricky conditional sum in Scripting

Hi Mark,

Check the attachment and let me know.

markgraham123
Valued Contributor

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.


markgraham123
Valued Contributor

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,

MVP
MVP

Re: Tricky conditional sum in Scripting

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,

];


t1.png

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

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 NumberSecondary NumberA to BB to CC to DFinal TransitType
12345556773112Special
12345278651367Regular

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

markgraham123
Valued Contributor

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 NumberSecondary NumberA to BB to CC to DFinal TransitType
12345556773112Special
12345278651367Regular

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.

View solution in original post