Dear Experts,
I have data from a single table without an "obvious" relationship but whose data I need to group. I would like to create a group using formulas in crystal or SQL if possible that will allow me to create some grouping for the data.
The data is as follows :
ItemCode Qty TreeType Linenum
A100 1 S 0
A101 2 I 1
A102 3 I 2
B100 3 S 3
B101 2 I 4
B102 2 I 5
C100 5 N 6
I would like to group the data as follows :
ItemCode Qty TreeType Linenum
A100 5 S 0
B100 4 S 3
C100 5 N 6
One thing for sure is that after every 'S' treetype there is an 'I' and the item code with the 'S' is the parent item.
Hence for items that come immediately under tree type 'S' are summed up and the quantity is made to the total group quantity. The Quantity of the tree type 'S' is not added up but ignored. Where the tree type is N, the data is left as it is.
How can I create a group that runs such that after every 'S' tree type all the subsequent 'I' tree types are grouped together ?
I have data from a single table without an "obvious" relationship but whose data I need to group. I would like to create a group using formulas in crystal or SQL if possible that will allow me to create some grouping for the data.
The data is as follows :
ItemCode Qty TreeType Linenum
A100 1 S 0
A101 2 I 1
A102 3 I 2
B100 3 S 3
B101 2 I 4
B102 2 I 5
C100 5 N 6
I would like to group the data as follows :
ItemCode Qty TreeType Linenum
A100 5 S 0
B100 4 S 3
C100 5 N 6
One thing for sure is that after every 'S' treetype there is an 'I' and the item code with the 'S' is the parent item.
Hence for items that come immediately under tree type 'S' are summed up and the quantity is made to the total group quantity. The Quantity of the tree type 'S' is not added up but ignored. Where the tree type is N, the data is left as it is.
How can I create a group that runs such that after every 'S' tree type all the subsequent 'I' tree types are grouped together ?