Top N Column 1

[walls]

I'll start by saying that i'm a newbie.

current conditions:
crystal 9.0
1 table (is a log)
17 of 30+ columns represent time under diferent conditions

Problem:
bring the top 5 five values and id from wich colum came.

Any sugestions?
Were should i start?

Thank You

 

[lbass]

Please provide some sample data and also tell us the data type of the time fields--are they really times? Or seconds? Or? What is the unique field per row? Are you looking for the top 5 per row, or the top 5 across rows and columns?

-LB

 

[walls]

Here is the seudo structure:

id;timestamp;machinenumber;uptime;downtimereason1;downtimereason2;...;downtime reason17

they are minutes but they are cumulative at the en of the day al of them return to zero and start again,

so far..... i can create the groups base selected by dates and/or times, summaries of the columns but i DON'T want to show
all 17 columns i what to restrict the report to only the top 5 colums based on the bigest downtimes per row, i can get the total of each column in a time frame but my problem is how to hide or not show those columns that are not important in the time span of the printed report.

 

[lbass]

I'm not really following this. I only see one time field in your field list, not 17, and you didn't really state the datatype or supply an actual data sample. When you say you only want to show the top 5 columns based on the biggest downtimes per row, it implies that there might be a different set of 5 columns for different rows. While you might be able to suppress the ones not in the top5, you would then have a scattering of visible fields appearing across the 30 columns. As you can see, more explanation and an actual sample of data, along with a sample of how you would like the report to look, would be very helpful.

-LB

 

[walls]

I don't know if this sample will work but here is the data.

so...

1st thing is the date group: i ask for the datetime1 and datetime2.

2nd I group data by machine

3rd I get the maximun value for each column for each group

and finally my problem: show only the 5 biggest values for each machine.

index timestamp machnumb downtmaint downtauto downtsetup downtclnmach downtclntool downttoolrep downtprocess downtengrun downtmatissue downtstartup downtvalidate downtsecequip downtnocustpkg downtnosched downtnooper downtnomat downtnoinput
7088 9/30/2005 13:10 180 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
7089 9/30/2005 13:11 167 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
7090 9/30/2005 13:11 300 1343 363 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
7092 9/30/2005 13:11 167 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
7093 9/30/2005 13:11 300 1343 364 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
7094 9/30/2005 13:10 180 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
7095 9/30/2005 13:11 167 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
7096 9/30/2005 13:11 300 1343 366 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

 

[lbass]

So in your sample, you would want to see the following?

Mach
167 0,0,0,0,0
180 0,0,0,0,0
300 1343,366,1,0,0

Or what would your report look like?

Also I only see one datetime field, even though you mentioned that you ask for datetime1 an datetime2. Not sure what you meant by that.

-LB

 

[walls]

after i read again my reply i figure out that i was not clear

this is a log

the timestamp field is the when the entry to the table was done

machnumb= machine number

the rest are the cumulative minutes (mm.m) that the machine stop. each column represent a reason.

so in one day a machine can stop producing for several reason several times a day. every time that stops it is acounted in the apropiated field.

 

[walls]

yes the report should lok like this:

shift 1 (6:00am-5:59pm)
Mach
167 0,0,0,0,0
180 0,0,0,0,0
300 1343,366,1,0,0
...
xxx mmm,0,mmm,0,0

shift 2 (6:00pm - 5:59am)
mach
167 mmm,mmm,mmm,mmm,mmm
180 mmm,mmm,mmm,mmm,mmm
...
xxx mmm,mmm,mmm,mmm,mmm

 

[GJParker]

In your example are you not bothered about showing which of the downtimes are in the top 5 ? is it just the top 5 valus you wish to diplay.

So in your example you don't display that 1343 is downtmaint and 366 is downtauto.

shift 1 (6:00am-5:59pm)
Mach
167 0,0,0,0,0
180 0,0,0,0,0
300 1343,366,1,0,0






Gary Parker
MIS Data Analyst
Manchester, England

 

[walls]

I'm sorry i need also the head of the column

shift 1 (6:00am-5:59pm)

Mach# topreason1,topreason2,topreason3,topreason4,topreason5
167 0, 0, 0, 0, 0
180 0, 0, 0, 0, 0
300 1343, 366, 1, 0, 0

 

[GJParker]

but won't the top reasons be different for each machine ?



Gary Parker
MIS Data Analyst
Manchester, England

 

[walls]

yes your right ... i'm not been carefull on my post

Shift # x
Mach#

topreason1,topreason2,topreason3,topreason4,topreason5
167 0, 0, 0, 0, 0
topreason1,topreason2,topreason3,topreason4,topreason5
180 0, 0, 0, 0, 0
topreason1,topreason2,topreason3,topreason4,topreason5
300 1343, 366, 1, 0, 0

 

[lbass]

I think I would use "Add Command" as a datasource and use a union all statement to combine the reason fields, as in:

Select table.`index`,table.`timestamp`,table.`machnumb`, table.`downtmaint`, 'downtmaint' as reason
From `table` table
union all
Select table.`index`,table.`timestamp`,table.`machnumb`, table.`downtauto`, 'downtauto' as reason
From `table` table
union all
Select table.`index`,table.`timestamp`,table.`machnumb`, table.`downtsetup`, 'downtsetup' as reason
From `table` table//etc.
Order by 2 asc, 3 asc, 5 asc

Then you could insert groups on date, machine, and{command.reason}, and then insert a maximum on {command.downtmaint} which will now include all amounts per reason. You can then do a topN/group sort on "maximum of {command.downmaint}" to get the top 5 per machine. Your display would then look something like:

Mach
300 1343 downtmaint
366 downtauto
1 downtnoinput
0 (not sure what happens to ties)
0

-LB

 

[walls]

i will try your suguestion......

Thanks a Lot.

-OP

 

[walls]

It didn't work .... :[

the database engine is MYSQL 4.1.13
and i'm using ODBC to connect to it. (posible problem?)
the result query is ONLY THE FIRST SELECT.

here is a portion of your suguested query

(SELECT Timestamp,
Machnumb,
bpcsshopno2*10000+ bpcsshopno1 as ShopOrder,
currentcavs,
cycletime/10 as Cycle_Time,
(goodshots10k*10000)+ goodshots as Total_Good_Shots,
runthour+ (runtmin/60) as Run_Time,
downtmaint,
downtmaint as reason
FROM plcdatabase.plcrealtimedata)

UNION ALL

(SELECT Timestamp,
Machnumb,
bpcsshopno2*10000+ bpcsshopno1 as ShopOrder,
currentcavs,
cycletime/10 as Cycle_Time,
(goodshots10k*10000)+ goodshots as Total_Good_Shots,
runthour+ (runtmin/60) as Run_Time,
downtauto,
downtauto as reason
FROM plcdatabase.plcrealtimedata)

order by 2 asc, 3 asc, 5 asc

 

[lbass]

I'm surprised to see all those calculations in there. You shouldn't need the parens around the queries either, although I don't think it hurts. What is the error message you get?

The syntax/punctuation depends upon your datasource, so you might want to go to database->show SQL query in your original report to make sure you are using the right punctuation, etc.

-LB

 

[walls]

Yes there is a lot of calculations, and i did it just to avoid them in the report since i'm having hard time with it! , the data is comming from a machine and is not formated.

There is No error it just basically brings only the first SELECT of the query....

 

[lbass]

You should have the reason field like this in the first half of the query:

'downtmaint' as reason

and in the second half like:

'downtauto' as reason

Then check to see whether the reason field is showing both halves of the query. I think you'll find it does.

-LB

 

[walls]

I'm a newbie also in SQL .... as you can see.

now i understand what is your solution.

I let you know the result of the report.

 

[GJParker]

LBass suggestion of using UNION ALL is a good one, I had already started work on this formula so I'll post the solution anyway as an alternative.

This method will work with the data displayed in seperate columns as earlier requested.

This formula will create 2 arrays Value and Reason

//@Top5 (Place this formula in the mach number group header it can be suppressed)

WhilePrintingRecords;
Shared NumberVar Array Value;
Redim Value [5];
Shared StringVar Array Reason;
Redim Reason [5];

(
If {Mytable.downtmaint} <> 0 Then
    (
    Value[1]:= {MyTable.downtmaint};
    Reason[1] := 'downtmaint'
    )
);
(
If {Mytable.downtauto} <> 0 Then
    If {Mytable.downtauto} < Value[1] Then
       ( 
       Value[2] := {Mytable.downtauto};
       Reason[2] := 'downtauto'
       )
    Else
        (
        Value[2] := Value[1];
        Reason[2] := Reason[1];
        Value[1] := {Mytable.downtauto};
        Reason[1] := 'downtauto'
        )
);
(
If {Mytable.downtsetup} <> 0 Then
    If {Mytable.downtsetup} < Value[2] Then
        (
        Value[3] := {Mytable.downtsetup};
        Reason[3] := 'downtsetup'
        )
    Else If {Mytable.downtsetup} < Value[1] Then
        (
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := {Mytable.downtsetup};
        Reason[2] := 'downtsetup'
        )
    Else
        (
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := Value[1];
        Reason[2] := Reason[1];
        Value[1] := {Mytable.downtsetup};
        Reason[1] := 'downtsetup'
        )
);
(
If {Mytable.downtclnmach} <> 0 Then
    If {Mytable.downtclnmach} < Value[3] Then
        (
        Value[4] := {Mytable.downtclnmach};
        Reason[4] := 'downtclnmach'
        )
    Else If {Mytable.downtclnmach} < Value[2] Then
        (
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := {Mytable.downtclnmach;
        Reason[3] := 'downtclnmach'
        )
    Else If {Mytable.downtclnmach} < Value[1] Then
        (
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := {Mytable.downtclnmach};
        Reason[2] := 'downtclnmach'
        )
    Else
        (
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := Value[1];
        Reason[2] := Reason[1];
        Value[1] := {Mytable.downtclnmach};
        Reason[1] := 'downtclnmach'
        )
);
(
If {Mytable.downtclntool} <> 0 Then
    If {Mytable.downtclntool} < Value[4] Then
        (
        Value[5] := {Mytable.downtclntool};
        Reason[4] := 'downtclntool'
        )
    Else If {Mytable.downtclntool} < Value[3] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := {Mytable.downtclntool};
        Reason[4] := 'downtclntool'
        )
    Else If {Mytable.downtclntool} < Value[2] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := {Mytable.downtclntool};
        Reason[3] := 'downtclntool'
        )
    Else If {Mytable.downtclntool} < Value[1] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := {Mytable.downtclntool};
        Reason[2] := 'downtclntool'
        )
    Else
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := Value[1];
        Reason[2] := Reason[1];
        Value[1] := {Mytable.downtclntool};
        Reason[1] := 'downtclntool'
        )
);
//<----------------Repeat this block for all other reasons---------------------->
(
If {Mytable.downttoolrep} <> 0  and {Mytable.downttoolrep} > Value[5] Then
    If {Mytable.downttoolrep} < Value[4] Then
        (
        Value[5] := {Mytable.downttoolrep};
        Reason[5] := 'downttoolrep'
        )
    Else If {Mytable.downttoolrep} < Value[3] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := {Mytable.downttoolrep};
        Reason[4] := 'downttoolrep'
        )
    Else If {Mytable.downttoolrep} < Value[2] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := {Mytable.downttoolrep};
        Reason[3] := 'downttoolrep'
        )
    Else If {Mytable.downttoolrep} < Value[1] Then
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := {Mytable.downttoolrep};
        Reason[2] := 'downttoolrep'
        )
    Else
        (
        Value[5] := Value[4];
        Reason[5] := Reason[4];
        Value[4] := Value[3];
        Reason[4] := Reason[3];
        Value[3] := Value[2];
        Reason[3] := Reason[2];
        Value[2] := Value[1];
        Reason[2] := Reason[1];
        Value[1] := {Mytable.downttoolrep};
        Reason[1] := 'downttoolrep'
        )
);

//<----------------End of Block---------------------------------->

These arrays can then be displayed as columns in the report using formulas

Create seperate formulas for reason 1 - 5 and display in the mach number group header section

//@Reason1 
EvaluateAfter({@Top5});
Shared StringVar Array Reason;

Reason[1]

Create seperate formulas for value 1 - 5 and display in the details section

//@Value1
WhilePrintingRecords;
Shared NumberVar Array Value;

Value[1]

HTH


Gary Parker
MIS Data Analyst
Manchester, England