correctly looping/exiting a recursive sub

hi all,

Shg (& others) have previously mentioned that functions never need to be recursive, but in this case (nb: it's a sub rather than a function), recursion seems appropriate to me... even if I can't quite make it work


I'm sorry I'm not very succinct in my below description, but I hope that the expected output string examples in column L (+ my attempted coding) will help clarify my aim.

---------------------
My aim is to provide a list of strings at the right hand side of the "Consolidated Query Info" sheet which states all the "Disk Files" which are used as "Data Source Files" (& recursively onwards...) for each row of data where:
- column H only contains a "dot".
- column C shows how many rows relate to the "query" listed in column E.
- column G should match column E (by convention) but that's not always the case.

If column H = "." then check what the Data Source file(s) in column F are, for each row relating to a specific Disk File in column G (they are consecutive for the number of rows listed in column C), and in turn what are the source files for each "Data Source file" by matching the Column F string with a number of consecutive rows with the same string in column G. Once the next set of Disk Files is identified in Column G, look at the "data source files" for these rows & on & on, until the string listed as a data source file, can no longer be found as a Disk File (show this as a " ( - )").
- Creating an output string (see column L) where each opening bracket ("(") can be interpreted as "...relies on..." & each closing bracket (")") represents the closing of the specific level of recursion.
- Note: the first phrase in the strings are currently the Query name from column E rather than column G.

--------------
I run the code via the MacroCaller sub which is near the top of the d__IdentifyingQryChains_ForWeb module. This macro calls BuildQC_ThisQryReliesOnTheseDSF which calls GetDataSourceFileNames which recursively calls itself. GetDataSourceFileNames currently errors out when processing row 119 (or the subsequent processing of row 396) of the spreadsheet & I've added a msgbox to Stop the code.
Current Output keeps looping until the macro is stopped whereas it should move on by itself!
"IQUSRLIB.SUPPLIER ( IQUSRLIB.FROZ_INV3 ( IQUSRLIB.FROZ_INV2 ( IQUSRLIB.FROZ_INV1 ( IQUSRLIB.FROZ_INV ( OSLTHLF3.INP60 ( - ) OSLTHLF3.INP35 ( - ) ) OSLTHLF3.INP35 ( - ) OSLTHLF3.INP15 ( - ) ) OSLTHLF3.INP80 ( - ) OSLTHLF3.INP84 ( - ) IQUSRLIB.FROZ_INV1 ( IQUSRLIB.FROZ_INV ( OSLTHLF3.INP60 ( - ) OSLTHLF3.INP35 ( - ) ) OSLTHLF3.INP35 ( - ) OSLTHLF3.INP15 ( - ) ) OSLTHLF3.INP80 ( - ) OSLTHLF3.INP84 ( - ) IQUSRLIB.FROZ_INV1 ( IQUSRLIB.FROZ_INV ( OSLTHLF3.INP60 ( - ) OSLTHLF3.INP35 ( - ) )..."

To help identify what is going wrong I have included code to create a Log file of various code sections throughout the macro (+ present the strings in the first blank column). The created log file is saved in the same folder as the workbook containing the code.

QUESTION:
I think that I have over-complicated the recursion & I'm going to have a go at simplifying & re-writing my code at work tomorrow. However, if anyone can identify what I've done wrong in the existing code, can you please let me know?
(eg byRef v byVal etc)
Any comments/code fixes would be very much appreciated.

Thanks in advance,
Rob

gidday Rylo,

Thanks for the starting point, it does look promising - I'll check this out when I get to work.
Edit: If I take this approach, I'll try & convert it to use in arrays (variant or otherwise) for speed, but I'll get this one working first.

Rob

(ps: I'm using this as a bump for the UK locals ;-))

hi Rylo,

When I first ran the code I said to myself "WOW, that's fast for cell by cell interrogation!", unfortunately this only goes one level deep in the below section:

Please Login or Register  to view this content.

For example in row 37, your version returns
"IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( - ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )"

However, if we chase each element right back (recursively, for discussion's sake?) cells L37:L40 show a final string of:
"IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV2 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV3 ( OSLTHLF3.INP1501 ( - ) ) )"

which shows how IQUSRLIB.JH_DIMSDV5 (cells G472:G475) relies on the data source files listed in cells F472:F475 (ie IQUSRLIB.JH_DIMSDV4, IQUSRLIB.JH_DIMSDV1, IQUSRLIB.JH_DIMSDV2, IQUSRLIB.JH_DIMSDV3) and, in turn...
IQUSRLIB.JH_DIMSDV4 (Cell F472) as a data source file is found as a Disk File in cell G2055, and relies on the data source file of OSLTHLF3.INP15 (cell F2055), which in turn...
can't be found in the Disk File list therefore represent it as a " ( - ) " with an extra ")" because there is only "1" (cell C2055) data source file for IQUSRLIB.JH_DIMSDV4 (Cell G2055).
Then repeat for each of the above italicised items, so that a progressively longer string is created...

The repeated "in turn..." parts are what I can't wrap my head around (without the use of recursion), esp considering that each time you search for a Disk File name after having it listed as a Source file, you need to search from row 4 downwards again.
Is it possible to do this quickly without recursion?

Some more background information:
These strings represent the dependencies of query chains in an AS400 system. I'm trying to represent the query chain as a single string for the next part of the project which will allow me (or others) to search the single output column & identify any & all rows which are reliant on a specific Disk File. I can then represent the relevant strings visually by splitting out the strings into a group of indented "trees". I have a working macro for this but if I have time (to teach myself) I may try & do this using xml or nodes etc.


Rob

hi Rylo,

Yes, you did mention the breakpoints & I'm struggling at how to describe it with more clarity.

For typing speed here are some abbreviations:
Qry = Query (column E)
DSF = Data Source File (column F)
DF = Disk File (column G)
QryToRunNext (column H)


The

If column H = "."

represents the start point

and there being a change in column E.

represents the number of rows to include in the first "set" of DSF

It may be easier if you visualise the "need" for recursion in a similar way to creating a List of all Folders & Sub-Folders (eg where I got recursion stuck in my head = http://www.cpearson.com/Excel/recursionAndFSO.htm).
Using this analogy, the DF can be compared to a Folder & the corresponding n. rows of related DSF can be compared to a group of Sub-Folders. Each of the DSF (sub-folders) is then looked at as if it is a DF (Folder), and so on until the particular DSF (considered as a Folder) no longer contains anything "below it" (ie the particular DSF string is not found in the DF column). This signifies the breakpoint for that particular DSF & I would like it represented by " ( - ) ". This needs to re-occur for each of the DSF at each "level" (eg as if it is down the Folder tree).

It is further complicated because each different starting point (col H = ".") may at any stage end up using the same info as a "this query relies on ..." string which has previously been created. I guess a comparison to the folder tree analogy becomes limited here... hmmm, perhaps if the recursive ListFolders function also mapped the folders below shortcuts to folders (instead of just to actual folders). To be honest, it may end up appearing a bit like mapping a "pick a path book" or a mapping movement on a Snakes & Ladders board!

I should be in bed, but I'm writing pseudo-code in terms which are as generic as possible & I'll post that before I knock off for the night.

Thanks
Rob

Rob, is all of the data in the workbook you posted relevant, or could unneeded columns be cleared? It makes my head hurt ...

hi Shg,

Lol, my head is hurting too ;-)

Sorry, I had done a fair bit of tidying as the original file goes out beyond column BE!

The below columns can be hidden or deleted:

• Column A
  (This sets the size of the DNR's used in my current code - it's a hangover from my unsanitised file. You can delete this column if you don't rely on the DNR.)
• Column B
  (purely reference only, but shows order because the order of the DSF is significant)
• Column C
  (identifies how many DSF exist for the conscutive rows that have the same DF. It is part of my source data & I had intended to use this to prevent extra looping to identify the changes in Column G (or alternatively column E).)
• Column D
  (not sure why I left this in)
• Column E
  (Rylo, if hiding this column you can instead refer to the changes in column G, which is probably easier anyway(?).)
• Column I
  (Originally included a Match function to state either: the numeric array element (row number = element + 3, where 3=number of header rows) that the DSF was first found as a DF; or a string stating the DSF is not listed as a DF. My intent was to use this to prevent excessive repeated looping in the code.)
• Column J
  (not sure why I left this in)
• Columns M & onwards
  (hide/delete)

This leaves columns F, G, H & L as the visible columns. Hopefully, that's a bit cleaner.


Rob

DNR = Do Not Resuscitate?

Sounds appropriate ...

LOL

That is so tempting - but fixing queries using the outcome of this thread will keep me & the rest of the (Reporting) team busy for the next few months preparing for a system change. So I don't think the boss will go for it as do not resuscitate

DNR = dynamic named range

Here's my psuedo-code:

Please Login or Register  to view this content.

btw, is it normal for using the "unordered list" to effect the text size like it has in my last post?

tia
Rob

Rylo may have this in hand, but can you walk through how you arrive at the result in L37?

hi Shg,

I'm open to any suggestions & we may get the best solution with all of our powers combined!

Uh oh!
It looks like I've made a mistake.

EDIT: 11/8/2010, RB: The below post is now amended to show the correct string, visual representation & applied logic for row 37.
EDIT 2: 14/08/2010, RB: The below amendment is now RE-CORRECTED to show the correct string, visual representation & applied logic for row 37.

PROPERLY Corrected: cell L37 should be stated as:
"IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV2 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV3 ( OSLTHLF3.INP1501 ( - ) ) ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )"

This can be represented visually as the below where each level of indentation can be read out as "... relies on the DSF...".

(hopefully it shows up using the html code tags)

HTML Code: 

The logic for this result is:

1) cell H37 = "." therefore create a string starting with the DF in cell G37
=> "IQUSRLIB.JH_BOWWAVE"

2) add spaces & brackets (for identifying levels & for using in later parsing etc)
=> "IQUSRLIB.JH_BOWWAVE ("

3) append the first of 4 (cell C37, or alternatively, count the rows until the DF in column G changes) DSF values from cell F37
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ("

4) check if the above DSF appears as a DF by reading cell I37. In this case the DSF doesn't appear as a DF therefore add the terminator string of " - )". This can be checked by using [ctrl +F] on column F using "THLFU.FI2AE010PX" as a search string.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - )"

5) append the second of 4 (cell C38, or alternatively, count the rows until the DF in column G changes) DSF values from cell F37.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
"

6) check if the above DSF (cell C38) appears as a DF by reading cell I38. In this case the DSF does appear as a DF, therefore look this up in the DF column. It will appear in cell G472 (where rownum = 472 = cell I38 + numberOfHeaderRows = 469 + 3 = 472). Append a space & an opening bracket to the Output string
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
("

7) Repeat steps 3, 4 & 5, ie:

8) append the first of 4 (cell C472, or alternatively, count the rows until the DF in column G changes = cells G472:G475) DSF values (cell F472).
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4"

9) check if the above DSF appears as a DF by reading cell I472. In this case the DSF does appear as a DF (in row 2052+3 = cell G2055) therefore add an opening bracket.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4 ("

10) append the first (& only) of 1 DSF value (cell C2055, or alternatively, count the rows until the DF in column G changes = cell G2055) .
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15"

11) check if the above DSF appears as a DF by reading cell I2055. In this case the DSF doesn't appear as a DF therefore add the terminator string of " - )". This can be checked by using [ctrl +F] on column F using "" as a search string.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - )"

12) Recognise that IQUSRLIB.JH_DIMSDV4 only has one DSF (see #10)), therefore add another closing bracket.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) )"

13) "walk back up the tree" to the previous set of "open brackets" ie IQUSRLIB.JH_DIMSDV5 (cell G472) & repeat for the 2nd DSF (cell F473, as per #8 plus 1) which is IQUSRLIB.JH_DIMSDV1. Note that only a space & (not an opening bracket) is placed in the end of the new string because it is not the first one in the list of DSF.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5
( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) "

14) Append the second of 4 (cell C473, or alternatively, count the rows until the DF in column G changes = cells G472:G475) DSF values (cell F473).
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ("

15) Check if the above DSF appears as a DF by reading cell I473. In this case the DSF does appear as a DF (in row 76+3 = cell G79) therefore add an opening bracket.
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ("

16) append the first (& only) of 1 DSF value (cell C79, or alternatively, count the rows until the DF in column G changes = cell G79) .
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - )"

17) repeat for the other DSF files... until you get a final string of:
=> "IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV2 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV3 ( OSLTHLF3.INP1501 ( - ) ) ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )"

END EDIT
END EDIT2

tia
Rob

hi guys,

It's unlikely I'll have a chance to finish off my logic for the above example until tonight.
I've been thinking about my choice of analogy & perhaps a better one would "keep your hand on the left hand wall as your walk through the maze"

Rob

hi all,

Thanks for your perseverance so far - I hope you're still with me?

Snb,
Thanks for posting, unfortunately your code has the same shortfall that I described in post 4, regarding Rylo's version ie:

For example in row 37, your version returns
"IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( - ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )"

which is only "one level deep" & I need it to go as "deep" as it can for each DSF by repeateadly searching for it as a DF & then recursively for "any children" when it is found as a DF.

Rylo/Shg,
I have edited my post #12 to show the correct outcome for row 37.
NOTE: I haven't changed this in the example file of the first post.

Rob

hi Snb,

I'm testing this now & will let you know how it goes.

What was the run time on your machine?

- I know I should test on a smaller data set but I'm excited! :-)

Thanks
Rob

hi Snb,

I figured out my first problem - I had some nonsense cells at the base of my sheet which caused bloat in the "currentregion" which prevented it exiting correctly at the end of the second do loop.

Now I have a type mismatch error when writing the array to the sheet & assume it's because of application.transpose limitations as discussed in the below links (do they still apply in Excel 2007?)
http://www.dailydoseofexcel.com/arch...nge-using-vba/
http://www.officekb.com/Uwe/Forum.as...ize-limitation

I'll put it through Alan Beban's/another transposer before writing to the sheet - once I get home...


Thankyou so much - I'll let you know how it goes.

Rob

Nope - well... it's not only the transpose (if that's an issue at all).

The type mismatch seems to be caused by

Please Login or Register  to view this content.

I'll keep trying.


hometime...

Rob

Duh me!

Thanks Snb, yes, that should have been obvious.

I'm playing with the code now but I'm also uploading the most recent version of the file with code which includes a call to a log file (in the same folder) to try & spot what's happening and when.
This file shows the correct result for row 37 (as corrected in post # 12). I'm having some computer problems at the moment & Excel freezes while running the code (I'm might leave it running over night).

If it doesn't spit something out, I'll bite the bullet & use a smaller data set for testing - I'll let you know how it goes...


Thanks
Rob

hi guys,

Snb,
to get a "smaller data set" I changed all the dots in H120:H3590 to "ignore" to allow the primary If statement to be bypassed. I don't think it should have any impact on the processing of the H4:H119 rows that do have dots ("."), but I got a row 37 result of

IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_CURCONV ( - ) ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) ( THLFU.FI2AE010PX ( - ) ( THLFU.FI2AE010PX ( - ) )))

which is okay up until the "IQUSRLIB.JH_DIMSDV5" section.

------------
Rylo,
It seems more promising as it is recognising there is more than one level, however when I ran it*, good ol' row 37 still causes hiccups, the result from sub ccc is:

IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( ( 0( 0( 0( 0OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )

While my aim is to get the following string:
EDIT, 14/08/2010: string corrected as spotted by Rylo in point 4 of post # 28 )

IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV2 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV3 ( OSLTHLF3.INP1501 ( - ) ) ) OSLTHLF3.INP35 ( - ) IQUSRLIB.PROD_CELLS ( - ) )

END EDIT
*I copied & paste special'd the formulae to prevent repeated calls to the UDF & calculation but I don't think this should have had any impact.
btw, I'm reasonably sure that the file I uploaded in post 21 has no structural/column changes. The only differences are the new file has all the code examples, I've hidden some of the columns for Shg & you guys and deleted some of the results columns that were to the right of column L.



--------
I'm going to have another try at a recursive version, but I'll test any further suggestions as I go - shall we say that the race is on

I've spent some time earlier tonight glancing at the latest offerings in the "Bad threads..." and just want to be sure I'm not becoming statistic... do you need any more details about the logic?

Thanks for the ongoing help

Rob

... just want to be sure I'm not becoming statistic

We should all check that thread periodically ...

lol! :D

Yes, I agree. I think I'll forward the link to the "predicting random numbers" through to my team mates (a Reporting team for a group of Logistics Planners). Occasionally, both us & the Planners feel like a random number generator would be as good as any other report when interpreting some of the received forecasts!
Then again at other times, the forecasts can be fantastically accurate - I guess it's all swings & roundabouts...

Rob

I misinterpreted you question previously.
It's simpler than I thought
So this is my new suggetion.

Please Login or Register  to view this content.

If you want to check the result of the first phase you can use:

Please Login or Register  to view this content.

before

Please Login or Register  to view this content.

and add a stop:

Please Login or Register  to view this content.

If I rewrite your row37 example:

IQUSRLIB.JH_BOWWAVE ( THLFU.FI2AE010PX ( - ) IQUSRLIB.JH_DIMSDV5 ( IQUSRLIB.JH_DIMSDV4 ( OSLTHLF3.INP15 ( - ) ) IQUSRLIB.JH_DIMSDV1 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV2 ( OSLTHLF3.INP1501 ( - ) ) IQUSRLIB.JH_DIMSDV3 ( OSLTHLF3.INP1501 ( - ) ) ) OSLTHLF3.INP35 ( - ) OSLTHLF3.INP35 ( - ) )

[E37] (
[F37] (-)
[F38] ( [G469] )
[F39] ( - )
[F40] ( - ) )

Is this the correct interpretation ?

hi all,

Thanks guys
I've got 1/2 a day of meetings today, but I'll test your offerings & then go through & put in some more (correct) examples when I can later today.

Snb,
The literal "469" needs to be replaced by the string of dependent items it refers to
EDIT: BUT, I've also stuffed up the string that should actually be returned (as per below comments). /End EDIT

Rylo,
In the immortal words of the classic Kiwi' Toyota tv ad - Buggar!
- I've just become one of the statistics...
re point 2) I haven't checked yet, but there was one set that loops on itself & I should have removed it (in my first code I had listed the problems on the Exclusion Table sheet). I should have also deleted the rows from the "Consolidatd Query Info" sheet.
re point 4) sigh, I've given incorrect "expected outputs" again.


Thanks
Rob

I think I found it at last.
Make sure row 2 contains data (for currentregion)
I reduced the results to the first 100 rows (to save 'some' processing time).

Please Login or Register  to view this content.

hi Snb,



It looks good so far!

Using the smaller set of "... to 100" row 37 seems correct at a glance, I'll keep investigating, do a few more manual comparisons & let you know - probably tonight...

Rylo,
Sorry, the examples are still coming - probably tonight as well...

Thanks
Rob

Did some slight alterations to speed the thing up.
Record 1182 contains a circular reference that blocks the code.

Please Login or Register  to view this content.

hi guys,

I need to do some more random testing but I think snb has got us there & the speed is fantastic!
Thanks heaps :-)


I've checked & confirmed the results of Snb's code for our old friend "IQUSRLIB.JH_BOWWAVE" (row 37) & also a large one further down the page "IQUSRLIB.SP27" (row 3185). These both give the correct results & I have put them in a visual layout on the new sheet in the attached file.

I'll mark the post as solved on Monday, after I've had someone else do some random validation testing.

To let the next stage of the project work* I've made some small changes snb's code (see the SnbsCode module) to where the spaces are placed in the output & have modified my next macro (see the "e_VisualPresentationOfQueries" module) to ignore double spaces. This macro splits the strings down into a visual & vaguely hierarchical layout. For my testing I have it set up to work on the active cell on the added "Qry Chains' Hierarchical Layouts" sheet.

Rylo,
I'm sorry, when I spotted how close Snb's suggestion was, I made less effort to modify your suggestion. So I'm not sure what causes the circular reference in those few rows (1375, 1468^, 1901, 1951, 2022), & they'll definitely be part of the validation testing on Monday.
In case you are still interested in the challenge of a successful solution - I've noticed some occasional zeros coming through, for example row 124 results in:

IQUSRLIB.SAF_PLANT ( OSLTHLF3.INP35 ( - ) 0IQUSRLIB.JH_DIMSDV5 ( - ) )

^"IQUSRLIB.EPG09XSAF3" on row 1468 may be causing issues because it does not meet the business convention of naming the Disk File (ie the "query output", column G) with the same name as the Query (column E).


-----------
*I don't need help with the next stage of the project but just as an fyi, here is the aim...

• - be able to enter a search string which
• - is used to create a unique list of the "ThisQueryReliesOn" strings (what we've been working on).
• - on a separate sheet create the visual layout of each unique "ThisQueryReliesOn" string (one below the other).

Once again,
Thank you both for all your help
Rob



-----------
keywords for searchers:
AS400, "ISeries", "Query to Run Next", build query chain string
-----------

Good point regarding the unique list - I don't have a very good reason, just a thought in the back of my mind that, try as I might, I'll find that the others in the team want to work from the raw data (rather than a UI). Or, that I don't get the UI finished before other investigation/redevelopment needs to start. Also, the data is currently in the necessary Sort order so we can glance at the above row to see the string. However, this may not be the case when someone uses a different Sort Order on the page.
... I guess, some of this is me "being lazy" using Excel as a flat database & replicating the info on each row, instead of popping the whole lot into Access (Excel's definitely my comfort zone).

hmmm,
It took me a couple of re-reads but, yes I agree (now that it has sunk in), we don't need to reassign the value to itself.

Thanks
Rob

lol!

Yes, I'll try & be careful

Thanks for all your help

hi guys

I've had the "all clear" from some random testing by an office mate but I'll leave the thread open for the moment for general discussion...

Thanks Snb,
I haven't put your combobox in place quite yet as there's another step before that, which I wanted to try & work out myself before asking for help - I may be posting that soon!

Rylo,
I know the feeling of not being able to let go. In fact, I've just been reading an old blog which suggests that programmers are people who can't leave a rhetorical question unanswered (http://www.codinghorror.com/blog/200...to-heaven.html).

I keep losing count when I go through the brackets!
They should come in matched pairs with each opening & closing pair defining a single level. My home made splitter ignores all sequential closing brackets (at the end of the string) so it hasn't jumped out as an issue for me. The splitter is made up of CreateVisualRepOfQryChains & CheckAndAdjustInputColumn* which are stored in the e_VisualPresentationOfQueries module.
*should probably be a Function...

-------------------


btw, there's been a bit of a "requirements drift...", in other words, I have more understanding (or perhaps just a different perspective) of my required output, so here's my latest mutation of snb's code that I'm now using. This code has a very blunt Select Case approach to bypassing rows with circular reference/other issues. nb: I may have bypassed extra rows in error, but I'm pretty sure they are all related.

Please Login or Register  to view this content.

Rob

Originally Posted by broro183

...The splitter is made up of CreateVisualRepOfQryChains & CheckAndAdjustInputColumn* which are stored in the e_VisualPresentationOfQueries module.
*should probably be a Function...

After typing my "should probably be a function..." I thought about it & decided that I'm not using the CheckAndAdjustInputColumn code elsewhere, so I've merged the two subs together. Here's the modified Splitter code I'm using in my actual file (but it should work in the uploaded sample):

Please Login or Register  to view this content.

Rob