Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
markgraham123
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,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.

20 Replies

Re: Tricky conditional sum in Scripting

Hi Mark,

Check the attachment and let me know.

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

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

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

Community Browser