| Author |
Message
|
| anon_kb |
 Posted: Sun Jan 31, 2016 10:59 pm Post subject: GROUP AND SUM UP? NEED ADVICE |
 |
|
Acolyte
Joined: 13 Nov 2014
Posts: 74
|
Hi guys,
I need your advice or idea on this requirement.
| Code: |
[quote][b]Input:[/b]
<Main>
<Detail>
<LineNumber>11</LineNumber>
<Quantity>102</Quantity>
</Detail>
<Detail>
<LineNumber>11</LineNumber>
<Quantity>121</Quantity>
</Detail>
<Detail>
<LineNumber>12</LineNumber>
<Quantity>102</Quantity>
</Detail>
<Detail>
<LineNumber>12</LineNumber>
<Quantity>103</Quantity>
</Detail>
</Main>
[b]Output[/b]
<OutMain>
<OutDetail>
<OutLineNumber>11</OutLineNumber>
<OutQuantity>102</OutQuantity>
<OutClass>A</OutClass>
<OutPack>B<OutPack>
<OutTotal> ? <OutTotal>
</OutDetail>
<OutDetail>
<OutLineNumber>11</OutLineNumber>
<OutQuantity>121</OutQuantity>
<OutClass>A</OutClass>
<OutPack>B<OutPack>
<OutTotal> ? <OutTotal>
</OutDetail>
<OutDetail>
<OutLineNumber>12</OutLineNumber>
<OutQuantity>102</OutQuantity>
<OutClass>C</OutClass>
<OutPack>D<OutPack>
<OutTotal> ? <OutTotal>
</OutDetail>
<OutDetail>
<OutLineNumber>12</OutLineNumber>
<OutQuantity>103</OutQuantity>
<OutClass>C</OutClass>
<OutPack>D<OutPack>
<OutTotal> ? <OutTotal>
</OutDetail>
</OutMain>[/quote] |
Requirement: Value of <OutTotal> is the sum of Quantity that is group by (Input=LineNumber, Output=OutClass, Output=OutPack). So meaning in the sample since Detail with LineNumber had the same values in the group then their Quantity will be summed up. I understnd the logic but I dont know how to start it in ESQL? Maybe SELECT SUM or LOOP? Any suggestions guys? Really appreciate your help.
Thanks. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Mon Feb 01, 2016 5:33 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Well there is like a little problem. In your input sample, you do not have any references to Outclass , or outpack, or any field that could be mapped to those...
So how do you expect to group to non existent values? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| anon_kb |
| Posted: Mon Feb 01, 2016 7:48 pm Post subject: |
|
|
Acolyte
Joined: 13 Nov 2014
Posts: 74
|
| fjb_saper wrote: |
Well there is like a little problem. In your input sample, you do not have any references to Outclass , or outpack, or any field that could be mapped to those...
So how do you expect to group to non existent values? |
Hi FJB, the values of Outclass and outpack has been coded on the esql. There values actually came from the database.
Edit:
I've done some code on which it works but I only filter it using the LineNumber and OutLineNumber. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Feb 02, 2016 5:24 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You might want to check it, but I don't recall the SELECT on the tree structure allowing you any aggregate functions or any ordering functions...
So you will have to program for it...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 02, 2016 6:52 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
ESQL select does not support GROUP BY or ORDER BY.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| anon_kb |
| Posted: Tue Feb 02, 2016 7:43 pm Post subject: |
|
|
Acolyte
Joined: 13 Nov 2014
Posts: 74
|
Hi guys,
Thanks for you reply.  |
|
| Back to top |
|
|
| maurito |
| Posted: Wed Feb 03, 2016 12:13 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| You can try the SELECT function with SUM |
|
| Back to top |
|
|
| timber |
| Posted: Wed Feb 03, 2016 3:34 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| For completeness...XPath 2.0 is supported in the Mapping node in Custom XPath transformations. So you might be able to do something clever with that. |
|
| Back to top |
|
|
| anon_kb |
| Posted: Wed Feb 03, 2016 7:59 pm Post subject: |
|
|
Acolyte
Joined: 13 Nov 2014
Posts: 74
|
Hi Maurito,
Yes, I can already SUM it up using SELECT SUM but I've use only the filter that both exists on Input and Output.
Hi Timber,
XPath 2.0? hmmm .. Its kinda new on me I'll try your suggestion. Thanks |
|
| Back to top |
|
|
| anon_kb |
| Posted: Thu Feb 11, 2016 10:36 pm Post subject: [SOLVED] GROUP AND SUM UP? NEED ADVICE |
|
|
Acolyte
Joined: 13 Nov 2014
Posts: 74
|
Hi guys,
Thanks for your suggestions I already solve this problem. I've created temporary variable for the Quantity in the output. In short My temporary Output fields became the Input then I map it out for the actual Output. |
|
| Back to top |
|
|
|
|
