topic Re: Tricky conditional sum in Scripting in QlikView

Tricky conditional sum in Scripting

markgraham123 — Mon, 23 Nov 2015 03:37:12 GMT

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

Re: Tricky conditional sum in Scripting

tamilarasu — Mon, 23 Nov 2015 04:39:36 GMT

Hi Mark,

Check the attachment and let me know.

Re: Tricky conditional sum in Scripting

markgraham123 — Mon, 23 Nov 2015 04:48:17 GMT

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

tamilarasu — Mon, 23 Nov 2015 05:00:59 GMT

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

markgraham123 — Mon, 23 Nov 2015 05:08:53 GMT

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

tamilarasu — Mon, 23 Nov 2015 06:01:05 GMT

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

Re: Tricky conditional sum in Scripting

jonathandienst — Mon, 23 Nov 2015 06:20:30 GMT

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,

];

Re: Tricky conditional sum in Scripting

markgraham123 — Mon, 23 Nov 2015 19:48:05 GMT

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

markgraham123 — Mon, 23 Nov 2015 19:48:19 GMT

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

Re: Tricky conditional sum in Scripting

tamilarasu — Tue, 24 Nov 2015 02:02:52 GMT

Mark,

Check the attachment.

Re: Tricky conditional sum in Scripting

markgraham123 — Tue, 24 Nov 2015 02:46:53 GMT

Thank you verymuch sir.

This is really a great help

Re: Tricky conditional sum in Scripting

tamilarasu — Tue, 24 Nov 2015 02:56:22 GMT

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

Re: Tricky conditional sum in Scripting

markgraham123 — Tue, 24 Nov 2015 14:42:01 GMT

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;

Re: Tricky conditional sum in Scripting

tamilarasu — Tue, 24 Nov 2015 14:58:08 GMT

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.

Re: Tricky conditional sum in Scripting

markgraham123 — Tue, 24 Nov 2015 15:10:20 GMT

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

tamilarasu — Tue, 24 Nov 2015 15:16:06 GMT

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

Re: Tricky conditional sum in Scripting

markgraham123 — Tue, 24 Nov 2015 15:20:24 GMT

Here is the attachment.

Re: Tricky conditional sum in Scripting

tamilarasu — Tue, 24 Nov 2015 15:29:51 GMT

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

markgraham123 — Tue, 24 Nov 2015 15:34:34 GMT

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;

Re: Tricky conditional sum in Scripting

tamilarasu — Wed, 25 Nov 2015 02:09:54 GMT

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

Set vChara = " 'P' ";

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