topic Previous row in script in QlikView

Previous row in script

Wed, 02 Dec 2015 10:05:06 GMT

Hi All,

I need have date ,product,region and district as fields in my script.

Now i need to get the previous volume for each date based on product.How can i achieve that??

Kindly help.

Regards,

Reena

Re: Previous row in script

tamilarasu — Wed, 02 Dec 2015 10:09:07 GMT

Hi Reenaabraham,

Try Peek() or Previous function.

Re: Previous row in script

tresB — Wed, 02 Dec 2015 10:10:51 GMT

Check this post: Peek() or Previous() ?

Re: Previous row in script

Wed, 02 Dec 2015 10:12:20 GMT

I tried it.but it is coming based on the date.i need to get it based on both product basis and also by date wise.

Re: Previous row in script

Kushal_Chawda — Wed, 02 Dec 2015 10:23:57 GMT

Provide some sample data with expected output to get the exact solution

Re: Previous row in script

parevrac — Wed, 02 Dec 2015 10:45:09 GMT

you need to use Peek('fieldname', RecNo()-1,'table') BUT you must sort the input table first in the order you need it so that the previous row always contains the values you want to compare.

Re: Previous row in script

simospa — Wed, 02 Dec 2015 10:46:19 GMT

Hi,

use Peek() or Previous() as indicated by the other users, use it ordering the data in the order you wish

For example somthing like this

LOAD product,region, district, date, volume, peek(volume) as previous_volume

FROM yoursource

ORDER BY product asc, date asc

Eventually use a GROUP BY too, it depends from your data.

Re: Previous row in script

Wed, 02 Dec 2015 10:49:39 GMT

Hi All,

Attaching the sample data for your reference.

Re: Previous row in script

sunny_talwar — Wed, 02 Dec 2015 11:19:37 GMT

You seem to have multiple products on a single date. What exactly is your expected output from the same you have provided?

Re: Previous row in script

Wed, 02 Dec 2015 11:34:31 GMT

Hi i need to get the previous date value for each product.

Date Product Volume Output

01-01-15 ABC 100 0

01-01-15 BCA 200 0

01-01-15 ACB 400 0

02-01-15 ABC 250 100

02-01-15 BCA 50 200

02-01-15 ACB 150 400

02-01-15 HIJ 150 0

Re: Previous row in script

sunny_talwar — Wed, 02 Dec 2015 11:40:54 GMT

This make sense, but what if you have multiple value for a product on a single day, may be like this:

Date Product Volume Output

01-01-15 ABC 100 0

01-01-15 BCA 200 0

01-01-15 ACB 400 0

01-01-15 ABC 100 0

02-01-15 ABC 250 100 -> Would this now be 200? since we have two entries on previous date?

02-01-15 BCA 50 200

02-01-15 ACB 150 400

02-01-15 HIJ 150 0

Re: Previous row in script

Wed, 02 Dec 2015 12:50:54 GMT

Yes..if the product is having more than 1 entry for single date.then it should be the sum.

Thanks,

Reena

Re: Previous row in script

sunny_talwar — Wed, 02 Dec 2015 13:15:22 GMT

This?

Script:

Table:

LOAD Date(DayId, 'YYYYMMDD') as DayId,

Date(Date#(WeekEndingDate, 'MM/DD/YYYY')) as WeekEndingDate,

DistrictIdName,

RegionIdName,

Product,

Volume

FROM

TestDATAVOLume.xls

(biff, embedded labels, table is Sheet2$);

Temp:

LOAD DayId,

WeekEndingDate,

Product,

Sum(Volume) as SumOfVolume

Resident Table

Group By DayId, WeekEndingDate, Product;

FinalTable:

LOAD *,

If(Product = Previous(Product), Previous(SumOfVolume), 0) as PreviousSumOfVolume

Resident Temp

Order By Product, WeekEndingDate;

DROP Table Temp;

Re: Previous row in script

Fri, 04 Dec 2015 08:44:09 GMT

Hi Sunny,

Thanks a lot for the reply.

it works for Product.

but if i need to consider both region and District what changes has to be done?

Regards,

Reena

Re: Previous row in script

effinty2112 — Fri, 04 Dec 2015 10:36:16 GMT

Hi Reena

This script gives this output, the volumes are aggregated by district, region, product and day:

Data:

LOAD DayId,

DistrictIdName,

RegionIdName,

Product,

Sum(Volume) as Volume

FROM

[..\Community\Previous row in script\TestDATAVOLume.xls] //change this path

(biff, embedded labels, table is Sheet2$)

Group by

DistrictIdName,

RegionIdName,

Product,

DayId;

NoConcatenate

DataWithPrevDay:

LOAD

if( DistrictIdName & '|' & RegionIdName & '|' & Product =

Previous(DistrictIdName) & '|' & Previous(RegionIdName) & '|' & Previous(Product),

Date(Previous(DayId)), '') as PreviousDayId,

if( DistrictIdName & '|' & RegionIdName & '|' & Product =

Previous(DistrictIdName) & '|' & Previous(RegionIdName) & '|' & Previous(Product),

Previous(Volume)) as PreviousVolume,

DayId,

DistrictIdName,

RegionIdName,

Product,

Volume

Resident Data Order by DistrictIdName,

RegionIdName,

Product, DayId;

drop Table Data;

Re: Previous row in script

sunny_talwar — Fri, 04 Dec 2015 11:24:16 GMT

Try this:

Table:

LOAD Date(DayId, 'YYYYMMDD') as DayId,

Date(Date#(WeekEndingDate, 'MM/DD/YYYY')) as WeekEndingDate,

DistrictIdName,

RegionIdName,

Product,

Volume

FROM

TestDATAVOLume.xls

(biff, embedded labels, table is Sheet2$);

Temp:

LOAD DayId,

WeekEndingDate,

RegionIdName,

DistrictIdName,

Product,

Product & '|' & RegionIdName & '|' & DistrictIdName as Key,

Sum(Volume) as SumOfVolume

Resident Table

Group By DayId, WeekEndingDate, Product, RegionIdName, DistrictIdName;

FinalTable:

LOAD *,

If(Key = Previous(Key), Previous(SumOfVolume), 0) as PreviousSumOfVolume

Resident Temp

Order By Key, WeekEndingDate;

DROP Tables Temp, Table;