Trying to work out "Rate"

[Anonymous]

I have a simple sheet showing
Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded

to which I have added Duration using the function =interval(End_Time-Start_Time,'mm')

I want to now add in there the rate of load i.e Rows_Loaded / Duration

However the figure I end up seems to be applying the formula to all the records for that Table....not to that Batch#

Can someone please give me some advice? Thanks

[tresesco]

Could you explain the requirement with a small example?

[jyothish8807]

Hi Oracleman,

Are you trying to find the time taken by the application to load?

Regards

KC

[Anonymous]

the data source gives you:

Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded

1 , Clienttab, 01:00, 02:00, 1200

1, Orderstab,01:30,03:00, 900

2, Clienttab, 01:00, 02:00, 2400

expecting the following

Batch# , Table_Name ,Start_TIme ,End_Time, Rows_Loaded, Rate_of_Load

1 , Clienttab, 01:00, 02:00, 1200, 20

1, Orderstab,01:30,03:00, 900,10

2, Clienttab, 01:00, 02:00, 2400 ,40

=interval(End_Time-Start_Time,'mm') works fine but

Rows_Loaded/ interval(End_Time-Start_Time,'mm') gives some ridiculously high figure....i clearly not grouping properly

[Anonymous]

Trying to find how quickly each table's data is being loaded into our datawarehouse by looking at number of rows loaded and the time taken in minutes

[markodonovan]

Hi there,

Have a look at this post from stevedark, very useful:

http://www.quickintelligence.co.uk/qlikview-load-performance/

Thanks

Mark

techstuffy.tv

[Anonymous]

Thanks Mark....will take a look. My particular problem is that we have stats for how long loads into our datawarehouse tables took from source systems. We want to be able to analyse load times. Typically they are from vsam systems into Oracle, The Oracle system (a dwh) will keep track of how long these loads took on each day (batch#) for each table.

By looking at the stats for the load times for each table on each day, we can see whether load performance is being affected by other activity on the host.

[jonathandienst · Accepted solution]

oracleman wrote:

=interval(End_Time-Start_Time,'mm') works fine but

Rows_Loaded/ interval(End_Time-Start_Time,'mm') gives some ridiculously high figure....i clearly not grouping properly

Its probably not the grouping that is the problem. QV date/times are in units of days (like Excel). Interval() is a formatting function - it does not change the underlying value. So you are recording rows per day, not per minute. you need this:

     Rows_Loaded / (End_Time-Start_Time) / (24 * 60)

[chriscammers]

Hey oracleman,

The problem you are having is that your times are not really being interpreted as "time" and then interval does not return an integer rather the 'mm' format represents a mask for a time value which is some fraction of 1. I took your sample data and wrote a sample script to take you through the various functions you need to use to convert the string to a time value and then convert the duration to an integer. The preceding loads are not necessary but I think it is helpful to separate the steps.

Load

Batch#,

Table_Name,

Start_Time,

End_Time,

Duration,

Rows_Loaded,

//Calculate the actual per minute rate

Rows_Loaded/Duration as [Load_Rate(PerMinute)];

Load

Batch#,

Table_Name,

Start_Time,

End_Time,

//calculate the Duration and return minutes as an integer

Num#(Interval(End_Time-Start_Time,'mm')) as Duration,

Rows_Loaded;

Load

Batch#,

Table_Name,

//Convert the time strings to time values

Time(Num(Start_Time,'hh:mm')) as Start_Time,

Time(Num(End_Time,'hh:mm')) as End_Time,

Rows_Loaded

Inline [

Batch# , Table_Name ,Start_Time ,End_Time, Rows_Loaded

1 , Clienttab, 01:00, 02:00, 1200

1, Orderstab,01:30,03:00, 900

2, Clienttab, 01:00, 02:00, 2400

];

[Anonymous]

This did the trick....

     Rows_Loaded / (End_Time-Start_Time) / (24)

MANY THANKS to all