Solved

Excel: translate data into flow

Posted on 2013-01-28
21
209 Views
Last Modified: 2013-01-29
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
Comment
Question by:Watnog
  • 12
  • 9
21 Comments
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Watnog,

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

Thanks,
Brian.
0
 

Author Comment

by:Watnog
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
The excel file is attached.
Thanks!
0
 

Author Comment

by:Watnog
Comment Utility
I'm in CET so I'm off for diner.
Good luck.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Watnog,

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

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
(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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility

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
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
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
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
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
 

Author Comment

by:Watnog
Comment Utility
I'm very happy with your 'levelling' code Brian, it serves my purpose.
Thanks for your efforts, and for your solution.

Cheers.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, Watnog.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now