Creating Groups

[macktruck]

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 ?

 

[hilfy]

Looking at the Item Code field - is the group on the first two characters ('A1', 'B1', etc.) or the first three ('A10', 'B10', etc.)?

Assuming it's the first two, I would create a formula like this to group on:

Left({table.itemcode}, 2)

Group on that formula. If the row with the 'S' is always the lowest number in the set you can then sort on Item Code to get the data in the correct order.

Now create a formula to do your sum:

If {table.treetype} = 'S' then 0 else {table.qty}

Sum this formula instead of the qty field to get the numbers you're looking for.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[macktruck]

Hilfy,

There is no naming mechanism on the item code and as such an item code could as well start with ZKL009 etc. Hence grouping with the prefix would not suffice as such.

Thanks,

 

[lbass]

But is there a convention where the parent code ends with a zero and children have sequential numbers? Please show samples of actual data for this field.

-LB

 

[macktruck]

There is no convention for the item codes. See the sample data below

DU05
A100
Z090
TR98OIKL
POGHJK98

 

[hilfy]

If there is no grouping on the item code, how do you know which items should be grouped together?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

macktruck,

I don't know what I'm seeing. I expected you to show several sets of parents with children, identified as such.

-LB

 

[macktruck]

Hi Hilfy,

The grouping should be as the parent item -- that is the item with tree type 'S' and the subsequent child items with tree type 'I' should be grouped together as one. where there is ever tree type 'S' all the subsequent 'I' should be grouped as one.

I don't know if this helps ? Does it ?

 

[hilfy]

Beyond the 'S' and 'I', how do you identify that they are grouped together? What are the rules for which I's go with a specific S?

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[fisheromacse]

macktruck - there has to be some field that is used to keep the correct 'I's with the correct 'S's.

what is the data sorted on?

If i sort by treetype, it would provide a much different display result than if it were sorted by a datetime stamp or an itemCode.

 

[macktruck]

fisheromacse,

The will always be ordered that way, at no time will they come mixed up with the 'S' and 'I'. As they are input from a diffrent table with that ordering in place.

Regards,