Looking up a previous row in an Excel sheet

[#1]

Hi,

I have a time series energy data in an Excel spreadsheet with intervals of 15 minutes. I will need to compare 2 rows to get the energy produced in the interval. In Qlikview, how do I access a previous record? I have a datetime field with date and time (15 minute intervals)

Thanks

-Sri

[Michiel_QV_Fan]

Add this in your load statement:

previous(datetime) as Previous_datetimefield.

Order your load by datetime.

[#3]

Michiel,

Thank you for your reply. I have the following load:

LOAD Project,

     [Serial No] as [SerialNo],

     DateTime,

...

     Energy...

I have the energy in 15 minute intervals in .xls in the datetime field. (Let us say I have Energy as 80 in 1/13/2014 15:00 and 78 in 1/13/2014 14:45. I want to calculate the energy in the interval i.e. 80 - 78).

How do I access the Energy field in the previous(datetime) record?

Thank you very much for your time

Sri

[jagan]

Hi Srikanth,

previous(datetime) will give you the previous record datetime and Previous(Energy) will give you the previous record energy.  Please explain what you exactly want to calculate the energy in the interval i.e. 80 - 78.  Can you come with an example and sample file.

Regards,

Jagan.

[jonathandienst · Accepted solution]

Hi

I think what you are looking for is this:

LOAD Project,

     [Serial No] as [SerialNo],

     DateTime,

...

     Energy,

     Energy - Previous(Energy) As EnergyUsed,

HTH

Jonathan

[#6]

Jonathan,

Great. That will work for me. I was looking for the Energy used during the interval and Energy - Previous(Energy) gives me that.

Now, if I want to use this field 'EnergyUsed' for additional calculation in the load script, is it possible?

I was trying to use this along with another field :

LOAD Project,

     [Serial No] as [SerialNo],

     DateTime,

      Irr,

...

     Energy,

     Energy - Previous(Energy) As EnergyUsed,

      EnergyUsed * Irr

...

It says the field energyUsed is not found (as it is not in the excel). Would like to use it in the script instead of expression.

Thanks a lot

Sri

[jonathandienst]

Sri

You need a preceding load for that (LOAD only refers to input fields):

LOAD *,

     EnergyUsed * Irr

;

LOAD Project,

     [Serial No] as [SerialNo],

     DateTime,

      Irr,

...

     Energy,

     Energy - Previous(Energy) As EnergyUsed,

HTH

Jonathan