Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Excel: translate data into flow

Hi Experts,

Please have a look at this...
 sql output in excel
This is a representation of a dependency chain that looks like this...
 Flow representation
My question: I created the chart manually, are there ways to make this happen automatically? Doesn't necessarily have to be a chart, anything that helps visualizing the flow would be great.

The process/logic is as follows:
1. The '0' level job (JOB-C)  is the job that is queried in the database: the + and - is relative to that one, so arrows start there
2. Jobs should be aligned per their number, in this case there are 7 levels
3. Jobs that are under/above each other have a relationship as long as the level goes up or down

I don't know if this can be achieved, your input would be greatly appreciated.

Thanks.
0
Watnog
Asked:
Watnog
  • 12
  • 9
1 Solution
 
redmondbCommented:
Watnog,

Some questions, please...
(1) Why does Sleep-B link to Job-D while Job-B doesn't link to Job-A?
(2) What would the list look like if Job-B did link to Job-A?
(3) What's a rough maximum for the no. of items in the list?

Thanks,
Brian.
0
 
WatnogAuthor Commented:
First of all, the arrows are from 'JOB-C' perspective, and for them to be in line with the 'levels' they are reversed for the predecessors.
In fact what is shown is not so much a flow but the relationhip of a particular job with predecessors and successors.

What happens in reality is that JOB-A, JOB-B and JOB-C start simultanously; they have no dependency on another job.
See this 'normal' flow chart..
Flow 2
(1) The reason is that the data file produced by Job-A/B/C/D is ftp-ed to another server and  must arrive in a particular order and in particular time frame. As is drawn here they are ftp-ed in ABCD order and the sleep jobs (once 30' and once 90') ensure that they land at the right time.
(2) I have no word in that, I have to take the sequence for granted
(3) might be a bit more than 15 levels (16 or 17)

Thank you.
0
 
redmondbCommented:
Watnog,

Before we continue, please clear up the following for me. I thought that your original flow-chart was showing a box per item, but now I see that Job-D is there twice and Job-A not at all - or is it simply that the diagram is incorrectly labelled?

Thanks,
Brian.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
WatnogAuthor Commented:
I just changed the arrows of the the minus levels without touching the boxes (I think...).
As far as I can see the boxes still show  the four data jobs with their corresponding ftp jobs + 2 sleep jobs for the necessary delays.
:-}

Thanks for embarking on this, I will try my utter best not to confuse you...
0
 
redmondbCommented:
Thanks, Watnog, but I was talking about "your original flow-chart" not the new one.

Edit: and its table. (Sorry, my turn to increase the confusion.)
0
 
WatnogAuthor Commented:
You are confusing me .... :-)
They look the same to me really as far as boxes and jobs are concernced.
Unless the browser would mess up and makes us look at different things...

Just to be on the sure side, I repost the 'original one', with arrows from the queried job perspective (JOB-C)....

flow 1 (bis)
0
 
redmondbCommented:
Watnog,

(i) Where is Job-A in the table?
(ii) Why is Job-D in the table twice?

Thanks,
Brian.
0
 
WatnogAuthor Commented:
Oh! Should have checked that. And you are right...
Getting clear is a crucial and not to be underestimated part of the exercise, you have me agree on that.

Tree
The excel file is attached.
SQL-Output-to-Flowchart.xlsx
0
 
redmondbCommented:
The excel file is attached.
Thanks!
0
 
WatnogAuthor Commented:
I'm in CET so I'm off for diner.
Good luck.
0
 
redmondbCommented:
Watnog,

Edit: I'm having a compete rethink on this question, so I've deleted its first version.

Regards,
Brian.
0
 
redmondbCommented:
Watnog,

(i) How do we know that Job-C links to Sleep-B and not vice-versa?
(ii) What would the table look like if the diagram showed a link from Job-B to Job-A-ftp?
(iii) What's a rough maximum for the no. of rows in the table?

Thanks,
Brian.
0
 
WatnogAuthor Commented:
(i)  JOB-C is the job we query in the database, so we know that is the starting point
(ii) will run such a scenario and let you know (my) tomorrow
(iii) 20

Cheers.
0
 
redmondbCommented:
Thanks, Watnog.

JOB-C is the job we query in the database, so we know that is the starting point
How do you want to pass this information to the macro?

Thanks,
BRian.
0
 
redmondbCommented:
Watnog,

Please see my previous post.

Sleep-A links to Job-A-FTP . We know the "direction" of this link because of their Levels, but how do we know in the first place that the two tasks are linked at all...
(A) ... because they both have the name "A"?
(B) ... because they're on consecutive rows?
(C) ... some other way?

Thanks,
Brian.
0
 
WatnogAuthor Commented:

W - JOB-C is the job we query in the database, so we know that is the starting point
B - How do you want to pass this information to the macro?


Good point. If we have multiple 0 jobs, we might need to tell the macro which one is the one we go after.
However, I don't think it makes any difference in the presentation since all 0 jobs are somehow linked to each other, and all 0 jobs will have their predecessors and successors listed.

All jobs are linked because otherwise they wouldn't be in the table.
About everything in the table is meaningful, that's why I think it can work: there's just enough information. But I could be wrong.

The answer is B: if the level jumps 1 level and the rows are consecutive there's a link.If the level jumps more than 1 then it is at the end of 1 line and goes back to first common level to start another.

So it can get pretty complicated, hence the need to come with a means to visualise this better.

Thanks again.
W.
0
 
redmondbCommented:
All jobs are linked because otherwise they wouldn't be in the table.
So, if there are any jobs not linked or multiple Level 0 jobs then I can randomly select which is linked to which?

I think it can work: there's just enough information.
Managing expectations - I'm still not sure. (BTW, is there another format in which you can export the information? It doesn't matter if it's human-unfriendly - we just want the server's definitive view of things.)

The answer is B: if the level jumps 1 level and the rows are consecutive there's a link.If the level jumps more than 1 then it is at the end of 1 line and goes back to first common level to start another.
I'm not sure that works...
(i) It doesn't explain the link between Sleep-B and Job-C-FTP.
(ii) IF Job-B had a second link (say, to Job-A-FTP or Job-A) how could it be shown ?

Thanks,
Brian.
0
 
WatnogAuthor Commented:
After a night's sleep came to conclusion that you are right.
The levels show just that, not how one is linked to another.
As long as there's only 1 job per level the link is obvious, when there are more you can't know.
The table that is returned by the sql reveals something but it misses the cherry on top.
Thank you for making that clear.
Nevertheless can you consildate that? Do you know of way to transpose the table so that the jobs come per level?
That would look like this (for example)...

JOB-A      -4            
JOB-A-FTP      -3            
SLEEP-A      -2      JOB-B      -2
JOB-B-FTP      -1            
JOB-C      0      SLEEP-B      0
JOB-C-FTP      1      JOB-D      1
JOB-D-FTP      2            

 
__________________________________________

I'm not giving up though and I hope you want to give it a try with different sql output (as you proposed).
This is how the 'full' database table looks (tailored to fit our scenario, the original has about 18K rows), and it is from that table that the sql extracts its level information above.

It looks like this (xlsx is attached):
 Table
The table shown here is an extract already, showing just the linked jobs. In reality the job we query  would need to be searched in 18K rows:  in the 'parent job' column (in case it wouldn't have a predecessor so wouldn't be a child) and in the 'child job' column. Each predeccesor (parent) would be checked in turn, and same for each successor (child).

Given:
- list of parent -> child relationships
- job to query (JOB-C)
Question: is it possible to 'flow' this?
I admit, it sounds over the top...

Parent-Child.xlsx



PS. If you want this second approach in a seperate EE-Question, let me know.
Whole lot of thanks.
0
 
redmondbCommented:
Watnog,

Do you know of way to transpose the table so that the jobs come per level?,
Please see attached.

give it a try with different sql output
I'm not sure whether you're proposing to use the parent/child file or if that's just a taste of the underlying database. If the former, then it seems that the Excel macro would effectively need to recreate what already exists - the database.
I'm not a SQL person, but if the two versions you've shown so far are the best that an export can do (you sure?) then wouldn't the sensible approach be for code to directly access the database, walking the hierarchy, outputting entries as it goes?

If you want this second approach in a seperate EE-Question,
As I mentioned, I'm not a SQL person, so opening a new question in the appropriate topic areas would seem to be the way to go.

Regards,
Brian.SQL-Output-to-Flowchart-V2.xlsm
0
 
WatnogAuthor Commented:
I'm very happy with your 'levelling' code Brian, it serves my purpose.
Thanks for your efforts, and for your solution.

Cheers.
0
 
redmondbCommented:
Thanks, Watnog.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now