Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

oracleman
New Contributor

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

Re: Trying to work out "Rate"

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)

11 Replies
MVP
MVP

Re: Trying to work out "Rate"

Could you explain the requirement with a small example?

jyothish8807
Honored Contributor II

Re: Trying to work out "Rate"

Hi Oracleman,

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

Regards

KC

Best Regards,
KC
oracleman
New Contributor

Re: Trying to work out "Rate"

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



oracleman
New Contributor

Re: Trying to work out "Rate"

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
Valued Contributor

Re: Trying to work out "Rate"

Hi there,

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

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

Thanks

Mark

techstuffy.tv

oracleman
New Contributor

Re: Trying to work out "Rate"

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.

MVP
MVP

Re: Trying to work out "Rate"

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
Contributor III

Re: Trying to work out "Rate"

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

];

oracleman
New Contributor

Re: Trying to work out "Rate"

This did the trick....

     Rows_Loaded / (End_Time-Start_Time) / (24)


MANY THANKS to all

Community Browser