Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Trying to work out "Rate"

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

11 Replies
tresesco
MVP
MVP

Could you explain the requirement with a small example?

jyothish8807
Master II
Master II

Hi Oracleman,

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

Regards

KC

Best Regards,
KC
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Specialist
Specialist

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
Not applicable
Author

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
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
chriscammers
Partner - Specialist
Partner - Specialist

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
Not applicable
Author

This did the trick....

     Rows_Loaded / (End_Time-Start_Time) / (24)


MANY THANKS to all