topic Re: Help Generating Data. Previous() rowno() in QlikView

Help Generating Data. Previous() rowno()

Mon, 13 Oct 2014 05:22:23 GMT

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

Re: Help Generating Data. Previous() rowno()

senpradip007 — Mon, 13 Oct 2014 05:57:38 GMT

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.

Re: Help Generating Data. Previous() rowno()

Mon, 13 Oct 2014 06:19:19 GMT

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

Re: Help Generating Data. Previous() rowno()

Mon, 13 Oct 2014 07:51:36 GMT

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

Re: Help Generating Data. Previous() rowno()

Mon, 13 Oct 2014 07:56:43 GMT

Thanks I have got what I wanted

Re: Help Generating Data. Previous() rowno()

MarcoWedel — Mon, 13 Oct 2014 22:32:19 GMT

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