Parent child hierarchy - from two columns

[T1969]

I am looking for help on a parent child hierarchy issue i have.

I have thousands of line items that need to move to the right by date movement in days "delay Day's" basically forward scheduling.

Example data
Item No Delay Day,s
1 - 10
1 - 10
1 - 10
2 - 0
2 - 0
22 - 20
22 - 20
123 - 0
44 - 0


33 - 0


Example child parent relationship data for above.
CHILD PARENT
1 - 22
1 - 33
1 - 44
2 - 14
2 - 22
22 - 123
22 - 124
123 - 125
44 - 0
33 - 0


What I want to return is a list of numbers and the maximum amount of days to move.

For example from the above if I could get an output like

Item No Move days
1 - 10
22 - 20
123 - 20
124 - 20
125 - 20
2 - 0
44 - 10
33 - 10

Any help in resolving this would be greatly appreciated or if it not possible please shout up.

[CJ_London]

difficult to relate column headings to the data. Remove spaces from the column headings and use dots so it is clear

e.g.

Item NoDelayDays
1......10

Also please describe in words and by example how you arrive at the final result from the two tables

Your data is simplistic so also please clarify the rules around the parent child relationship - can a parent have more than one child? can a child have more than one parent? Can a parent have a child which is a parent to another child. If so, can that continue on so that child is parent to another and so on. What is parent 0?

What is the significance of the first table having three 1 - 10 records - could they be 1 - 10, 1 - 11, 1-15? if so, which value do you choose? or do you add or average them?

[Micron]

If I get it, I think you'll need to UNION two queries. The first gets the days greater than zero where the Parent and Item number are the same. In the second, for each Parent, get the days greater than zero where the Child and Item numbers are the same. In both cases, the two tables would be inner joined on Item number = Child. If you don't know UNION queries, check them out as there are a few limitations. You might notice a speed increase if you use DISTINCT on the two select query statements and UNION ALL for the query that puts them together.

[T1969]

difficult to relate column headings to the data. Remove spaces from the column headings and use dots so it is clear

e.g.

Item NoDelayDays
1......10

Also please describe in words and by example how you arrive at the final result from the two tables

Your data is simplistic so also please clarify the rules around the parent child relationship - can a parent have more than one child? can a child have more than one parent? Can a parent have a child which is a parent to another child. If so, can that continue on so that child is parent to another and so on. What is parent 0?

What is the significance of the first table having three 1 - 10 records - could they be 1 - 10, 1 - 11, 1-15? if so, which value do you choose? or do you add or average them?

Ajax, Thanks for the response sorry for the confusion.

The list of move days is made up of various items that have no relationship to any other item at this stage, all independent of each other. Item has a move in days number.
What is the significance of the first table having three 1 - 10 records - could they be 1 - 10, 1 - 11, 1-15? if so, which value do you choose? or do you add or average them?[/QUOTE] - There will be only be one number for the days delay. The multiple entries are the same item having a build sequence that has the same number applied.

Move list
Item.....Days delay (move)
1.....10
1.....10
1.....10
2.....0
2.....0
22.....20
22.....20
123.....0
44.....0
33.....0

Then I have another list of parent to child.

• The child can have many parents.
• The child in the list could also be a parent.
• Both parent & child could be in the move list.
• What is parent 0? - mistake in my example as 0 meant no number.

Child ..... Parent relationship list
Child.....Parent
1.....22
1.....33
1.....44
2.....14
2.....22
22.....123
22.....124
123.....125


Output list I am looking for.

Item.....Days delay (Move)
1.....10
2.....0
22.....20
14.....0
123.....20
124.....20
44.....10
33.....10

The logic for the biggest group in this example being. I have missed some of the numbers from this example to try & simplify what I am after.
Item.....Days delay (move) = description
1.....10 = child to ITEM 22
Then
Item
22..... 20 = has a twenty day move of its own + a 10 day move from child ITEM 10 (max = 20 days move).
Then
Item
123.....20 = has a 20 day move from ITEM 22 (also ITEM 124 has the same 20 day move)
Then
ITEM
125.....20 = 20 day move from ITEM 123

Hope this makes a little more sense and hopefully has not confused my question further.

Thanks for your reply much appreciated.

[T1969]

If I get it, I think you'll need to UNION two queries. The first gets the days greater than zero where the Parent and Item number are the same. In the second, for each Parent, get the days greater than zero where the Child and Item numbers are the same. In both cases, the two tables would be inner joined on Item number = Child. If you don't know UNION queries, check them out as there are a few limitations. You might notice a speed increase if you use DISTINCT on the two select query statements and UNION ALL for the query that puts them together.

Micron,

Need help on setting up the two queries if you could please. I have had a go but have not managed to come up with the two lists. My skill level is basic. I have carried out a union of sheets via SQL in the past.

the two tables I have for trials are:

The move file



Child - Parent list

[Micron]

If you would post tables instead of pictures I'd grab the info and test it, since this one is a bit confusing. This is done by using Excel (which it looks like you are anyway) and when the columns are sized correctly and the data aligned properly, you just dump it in your post and get a nice table that can be copied. The first row of each table should be the table name, which you haven't provided. The second row should have each field name in a column. A results table would also be nice. In most complex/confusing cases (like this one), I prefer not to have to build my own data in case I get it wrong. Alternatively, you could just post a zipped Excel file.

[T1969]

If you would post tables instead of pictures I'd grab the info and test it, since this one is a bit confusing. This is done by using Excel (which it looks like you are anyway) and when the columns are sized correctly and the data aligned properly, you just dump it in your post and get a nice table that can be copied. The first row of each table should be the table name, which you haven't provided. The second row should have each field name in a column. A results table would also be nice. In most complex/confusing cases (like this one), I prefer not to have to build my own data in case I get it wrong. Alternatively, you could just post a zipped Excel file.

Micron,

Thanks for coming back to me, I have hopefully attached my test data file.

plan forward test.zip

[CJ_London]

Thank you for the explanation. What you appear to require is a recursive function (VBA).

A recursive function is one that calls itself one or more times.

One point is these usually work from the parent down, not from the child up, so I can see a problem


Move list deduped
Item.....Days delay (move)
1.....10
2.....30
22.....20
123.....0
44.....0
33.....0

just to illustrate the problem, I have changed the value for item 2 to 30

Child ..... Parent relationship list
Child.....Parent
1.....22
1.....33
1.....44
2.....14
2.....22
22.....123
22.....124
123.....125

So child 2 has a parent 22, and child 1 has the same parent. And parent 22 is a child of 123

So 'going up' from child 1 (10) to 22 (20) means 123 has a maximum value of 20

but 'going up' from child 2 (30) to 22 (20) means 123 has a maximum value of 30

So which is right? At the moment your logic is not clear to me.

[Micron]

Maybe this is close. However, your file contains a row for 'desired' for 125 but there is no data in either table for 125. Not sure why you included desired rows for zero moves. I seem to recall your original post excluded zeros. So this seems to provide all the other examples in your desired results, except there are no records for zero moves, nor is there a record for 125:

SELECT tblCP.[Item CHILD], tblDelay.Delay
FROM tblDelay INNER JOIN tblCP ON tblDelay.Item = tblCP.[Item CHILD]
WHERE (((tblDelay.Delay)>0))
UNION
SELECT tblCP.[Item PARENT], tblDelay.Delay
FROM tblCP INNER JOIN tblDelay ON tblCP.[Item CHILD] = tblDelay.Item
WHERE (((tblDelay.Delay)>0));

If the resulting field captions are not correct, you'll have to come up with captions that work.
If Ajax is on to something, my sql is probably not going to work beyond the small data samples you've provided.

[T1969]

Thank you for the explanation. What you appear to require is a recursive function (VBA).

A recursive function is one that calls itself one or more times.

One point is these usually work from the parent down, not from the child up, so I can see a problem


Move list deduped
Item.....Days delay (move)
1.....10
2.....30
22.....20
123.....0
44.....0
33.....0

just to illustrate the problem, I have changed the value for item 2 to 30

Child ..... Parent relationship list
Child.....Parent
1.....22
1.....33
1.....44
2.....14
2.....22
22.....123
22.....124
123.....125

So child 2 has a parent 22, and child 1 has the same parent. And parent 22 is a child of 123

So 'going up' from child 1 (10) to 22 (20) means 123 has a maximum value of 20

but 'going up' from child 2 (30) to 22 (20) means 123 has a maximum value of 30

So which is right? At the moment your logic is not clear to me.

Ajax,

The logic you have described is what I am looking for.

The problem is my organisation is using SAP to plan the business. Unfortunately we have a lot of orders in arrears and we are allowing backward scheduling in the past, this is to give a better signal to purchasing to buy raw material.

I am trying to look at a more realistic view of load and capacity. Moving anything from the past to start "today" and cascade forwards.

Your explanation above is exactly what I am looking for. If the parent has multiple children it will need to move to the right and cascade against the child with the biggest movement. This then flows up as that parent can become a child to another parent etc.

Hopefully this is achievable if not I will have to go back to the drawing board.

Thanks for your help

[CJ_London]

Well, the logic has to be parent down, not child up.

You have not answered the question regarding number of levels, if these are fixed or limited to a maximum number of relationships it can be done in one query, otherwise it will need to be VBA, but would be interested in Micron's union query option, it is not one I would have considered

[T1969]

Well, the logic has to be parent down, not child up.

You have not answered the question regarding number of levels, if these are fixed or limited to a maximum number of relationships it can be done in one query, otherwise it will need to be VBA, but would be interested in Micron's union query option, it is not one I would have considered

Ajax,

There can be a lot of levels dependent on the component I would say I have seen some up to twenty levels. They can all vary from one to multiple.

I have tried to re create Micron's and it seems from my attempts not to run past the first relationship. It could be how I have tried to re create though.

[CJ_London]

up to twenty levels is impractical for a single query

So think you will need to use a recursive function. Here is an example

http://www.cpearson.com/excel/recursiveprogramming.aspx

although it is an excel example, the principle is the same

the example is adding values, you would need to change that to comparing and if greater then use the higher value

[Micron]

The first table is the desired outcome from your spreadsheet. The second is the query output. As mentioned, if you consider that the tables have no record for 125, and my original post assumed you were ignoring rows with zero shifts (based on what you posted) I'd say the query is spot on when compared to your desired results. Not sure what you mean when you say "I have tried to re create Micron's and it seems from my attempts not to run past the first relationship" but it's probably due to your results being based on a much greater number of records, thus it's not performing as expected. If that's the case, I have no other suggestions based on the sample data provided, probably because I'm not fully grasping the data model - except for possibly this pedigree solution http://allenbrowne.com/ser-06.html

Item No	Move days
1	10
2	0
14	0
22	20
33	10
44	10
123	20
124	20
125	20

Item CHILD	Delay
1	10
22	10
22	20
33	10
44	10
123	20
124	20

[T1969]

up to twenty levels is impractical for a single query

So think you will need to use a recursive function. Here is an example

http://www.cpearson.com/excel/recursiveprogramming.aspx

although it is an excel example, the principle is the same

the example is adding values, you would need to change that to comparing and if greater then use the higher value

Ajax,
Thanks for your support and advise much appreciated.