Qlik Community

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
MVP

Re: Looking up a previous row in an Excel sheet

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

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

Re: Looking up a previous row in an Excel sheet

Add this in your load statement:

previous(datetime) as Previous_datetimefield.

Order your load by datetime.

Not applicable

Re: Looking up a previous row in an Excel sheet

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

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

Re: Looking up a previous row in an Excel sheet

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

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 :

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

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

LOAD *,

     EnergyUsed * Irr

;

LOAD Project,

     [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
Community Browser