# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

## Looking up a previous row in an Excel sheet

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

1 Solution

Accepted Solutions
MVP

## Re: Looking up a previous row in an Excel sheet

Hi

I think what you are looking for is this:

[Serial No] as [SerialNo],

DateTime,

...

Energy,

Energy - Previous(Energy) As EnergyUsed,

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
Valued Contributor

## Re: Looking up a previous row in an Excel sheet

previous(datetime) as Previous_datetimefield.

Not applicable

## Re: Looking up a previous row in an Excel sheet

Michiel,

[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

MVP

## Re: Looking up a previous row in an Excel sheet

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.

MVP

## Re: Looking up a previous row in an Excel sheet

Hi

I think what you are looking for is this:

[Serial No] as [SerialNo],

DateTime,

...

Energy,

Energy - Previous(Energy) As EnergyUsed,

HTH

Jonathan

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

## Re: Looking up a previous row in an Excel sheet

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 :

[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

MVP

## Re: Looking up a previous row in an Excel sheet

Sri

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

EnergyUsed * Irr

;

[Serial No] as [SerialNo],

DateTime,

Irr,

...

Energy,

Energy - Previous(Energy) As EnergyUsed,

HTH

Jonathan

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