Solved

Excel: translate data into flow

Posted on 2013-01-28
21
212 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
ID: 38826945
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
ID: 38827048
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
ID: 38827161
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
ID: 38827202
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
ID: 38827209
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
ID: 38827250
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
ID: 38827319
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
ID: 38827344
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
ID: 38827348
The excel file is attached.
Thanks!
0
 

Author Comment

by:Watnog
ID: 38827364
I'm in CET so I'm off for diner.
Good luck.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:redmondb
ID: 38827433
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
ID: 38827478
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
ID: 38827672
(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
ID: 38827691
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
ID: 38827841
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
ID: 38828406

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
ID: 38828489
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
ID: 38830233
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
ID: 38830761
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
ID: 38831409
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
ID: 38831437
Thanks, Watnog.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

863 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

22 Experts available now in Live!

Get 1:1 Help Now