Help Generating Data. Previous() rowno()

[#1]

I have following data what i need to do is create new variable for segregating the events in my data for reporting them separately. The new instance will start right after the Movement 7. I want to do this in script as I need to use it in multiple reports and the data size is huge. I hope I am clear with my requirement, if not please ask me what is not clear. I need to do this ASAP.

I have tried to use previous function but failed.

This is the data I have

Device	Date	Movement
A	1/10/2014	1
A	1/11/2014	2
A	1/12/2014	3
A	1/13/2014	4
A	1/14/2014	5
A	1/15/2014	7
A	1/16/2014	3
A	1/17/2014	4
A	1/18/2014	7
A	1/19/2014	1
A	1/20/2014	2
A	1/21/2014	3
A	1/22/2014	4
A	1/23/2014	7
B	1/10/2014	1
B	1/11/2014	2
B	1/12/2014	3
B	1/13/2014	4
B	1/14/2014	7
C	1/10/2014	1
C	1/11/2014	2
C	1/12/2014	3
C	1/13/2014	4
C	1/14/2014	6

This is what I want

Device	Date	Movement	New Field
A	1/10/2014	1	1
A	1/11/2014	2	1
A	1/12/2014	3	1
A	1/13/2014	4	1
A	1/14/2014	5	1
A	1/15/2014	7	1
A	1/16/2014	3	2
A	1/17/2014	4	2
A	1/18/2014	7	2
A	1/19/2014	1	3
A	1/20/2014	2	3
A	1/21/2014	3	3
A	1/22/2014	4	3
A	1/23/2014	7	3
B	1/10/2014	1	1
B	1/11/2014	2	1
B	1/12/2014	3	1
B	1/13/2014	4	1
B	1/14/2014	7	1
C	1/10/2014	1	1
C	1/11/2014	2	1
C	1/12/2014	3	1
C	1/13/2014	4	1
C	1/14/2014	6	1

EXCEL IS ALSO ATTACHED

[senpradip007]

Try like:

If(RowNo()=1 or Peek(Device) <> Device, 1, If(Peek(Movement)=7, Peek(NewField1)+1, Peek(NewField1)))  AS NewField1

PFA, hope it will help.

[#3]

It is ignoring the device. For new device number should start from 1

[#4]

Hi,

Use this script it is working for me. I am also attaching the QVD.

Tab1:

LOAD Device,

     Date,

     Movement

FROM

C:\Users\752202\Downloads\SampleData.xlsx

(ooxml, embedded labels, table is Given);

NoConcatenate

LOAD Device,

     Date,

     Movement,

     if(isnull(Previous(Movement)) or Previous(Device)<>Device,1,if(Previous(Movement)=7,Peek(New_Field)+1,Peek(New_Field))) as New_Field

     Resident Tab1;

  DROP Table Tab1;

Hope this helps.

Vivek

[#5]

Thanks I have got what I wanted

[MarcoWedel]

Hi,

one solution might be:

LOAD *,
     If(Device=Previous(Device),Peek([New Field])-(Previous(Movement)=7),1) as [New Field]
FROM [http://community.qlik.com/thread/137492] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco